This is an online shopping system, now need to show a user's information, which need to show the user's order, payment, to confirm the number of orders,
The users table, there is no record for payment, to confirm the number of orders on record in the table order ID, user ID, status, and other fields,
Question: how to only through a query, the user information (include generation of payment orders, orders for payment) query out?


SELECT the STATUS, the COUNT (*)
The FROM tb_order
WHERE userid='02000001'
GROUP BY the STATUS

CodePudding user response:

Select sum (decode (STATUS 'to pay', 1, 0)) for payment,
The sum (decode (STATUS, 'to be confirmed, 1, 0)) to confirm
The from tb_order
WHERE userid='02000001'

CodePudding user response:

The CREATE TABLE TB_ORDER (ORDER_ID NUMBER, CUSTOMER_ID NUMBER (5), the STATUS VARCHAR2 (30));
INSERT INTO TB_ORDER VALUES (1100-01, 'obligations);
INSERT INTO TB_ORDER VALUES (2100-01, 'TBC);
INSERT INTO TB_ORDER VALUES (3100-01, 'paid');
INSERT INTO TB_ORDER VALUES (4100-02, 'generation of payment);
INSERT INTO TB_ORDER VALUES (5100-02, 'to pay');
INSERT INTO TB_ORDER VALUES (6100-03, 'paid');
The SELECT CUSTOMER_ID, STATUS, COUNT (ORDER_ID)
The FROM TB_ORDER
GROUP BY CUSTOMER_ID, STATUS
;

CodePudding user response:

refer to the second floor cjp20160817 response:

CREATE TABLE TB_ORDER (ORDER_ID NUMBER, CUSTOMER_ID NUMBER (5), the STATUS VARCHAR2 (30));
INSERT INTO TB_ORDER VALUES (1100-01, 'obligations);
INSERT INTO TB_ORDER VALUES (2100-01, 'TBC);
INSERT INTO TB_ORDER VALUES (3100-01, 'paid');
INSERT INTO TB_ORDER VALUES (4100-02, 'generation of payment);
INSERT INTO TB_ORDER VALUES (5100-02, 'to pay');
INSERT INTO TB_ORDER VALUES (6100-03, 'paid');
The SELECT CUSTOMER_ID, STATUS, COUNT (ORDER_ID)
The FROM TB_ORDER
GROUP BY CUSTOMER_ID, STATUS
;

misunderstood the meaning of the building Lord, is to look at the title under just know line issues,
Attached statement:
SELECT * FROM (
The SELECT CUSTOMER_ID, STATUS, COUNT (ORDER_ID) CNT
The FROM TB_ORDER
GROUP BY CUSTOMER_ID, STATUS
)
The PIVOT (MAX (CNT) FOR the STATUS (IN 'to pay', 'to be confirmed,' paid '))
The ORDER BY CUSTOMER_ID
;