我有两个表需要从中提取数据。我需要在查询中生成订单总数。应该是直截了当的,但对于有多个订单的人,我得到的结果不准确。这是我的 DDL
Create Table #first
(
userid varchar(100)
,orderqty int
,orderprice float
,orderdesc varchar(500)
,orderdate datetime
)
Create Table #second
(
userid varchar(100)
,orderstatus varchar(100)
,orderreviewstatus varchar(100)
)
Insert Into #first VALUES
('emp12', '14', '10', 'firstorder', '2016-08-11 13:59:23.363')
,('bla', '1', '20', 'red socks', '2016-08-11 13:59:23.363')
,('gra23', '2', '4', 'black coat', '2016-08-11 13:59:23.363')
,('meh33', '5', '2', 'orange socks', '2016-08-11 13:59:23.363')
,('meh33', '3', '1', 'blue trousers', '2016-08-13 13:59:23.363')
Insert Into #second VALUES
('emp12', 'Shipped', 'Reviewed')
,('bla', 'Shipped', 'Reviewed')
,('gra23', 'Shipped', 'Reviewed')
,('meh33', 'Shipped', 'Reviewed')
,('meh33', NULL, NULL)
在此示例中,只有 userid meh33 有两个订单,这是我正在使用的查询
Select
a.userid
,a.orderqty
,a.orderprice
,SUM(a.orderqty*a.orderprice) As OrderTotal
,a.orderdesc
,a.orderdate
,b.orderstatus
,b.orderreviewstatus
FROM #first a
INNER JOIN #second b
ON a.userid = b.userid
GROUP BY a.userid, a.orderqty, a.orderprice, a.orderdesc, a.orderdate, b.orderstatus, b.orderreviewstatus
这是此查询产生的输出(注意 meh33 的多个条目:
userid orderqty orderprice OrderTotal orderdesc orderdate orderstatus orderreviewstatus
bla 1 20 20 red socks 2016-08-11 13:59:23.363 Shipped Reviewed
emp12 14 10 140 firstorder 2016-08-11 13:59:23.363 Shipped Reviewed
gra23 2 4 8 black coat 2016-08-11 13:59:23.363 Shipped Reviewed
meh33 3 1 3 blue trousers 2016-08-13 13:59:23.363 NULL NULL
meh33 3 1 3 blue trousers 2016-08-13 13:59:23.363 Shipped Reviewed
meh33 5 2 10 orange socks 2016-08-11 13:59:23.363 NULL NULL
meh33 5 2 10 orange socks 2016-08-11 13:59:23.363 Shipped Reviewed
这是我想要的输出:(每个订单一行)
userid orderqty orderprice OrderTotal orderdesc orderdate orderstatus orderreviewstatus
bla 1 20 20 red socks 2016-08-11 13:59:23.363 Shipped Reviewed
emp12 14 10 140 firstorder 2016-08-11 13:59:23.363 Shipped Reviewed
gra23 2 4 8 black coat 2016-08-11 13:59:23.363 Shipped Reviewed
meh33 5 2 10 orange socks 2016-08-11 13:59:23.363 Shipped Reviewed
meh33 3 1 3 blue trousers 2016-08-13 13:59:23.363
我必须在查询中更改什么才能返回我想要的结果集?
您需要更改GROUP BY和SELECT子句。另外,我会使用 Order_ID 而不是 description 来排序。