对和订单总数的查询

Question

我有两张表，我需要从中提取数据。我需要在查询中生成一个订单总数。应该是直接的，但我得到的结果不准确的任何人谁有多个订单。这是我的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 

为了返回我想要的结果集，我必须在查询中修改什么？

Answer 1

您需要更改GROUP并选择子句。另外，我会使用Order_ID而不是描述来进行排序。

Select a.userid ,
       SUM(a.orderqty) AS OrderQtyTotal ,
       SUM(a.orderprice) AS OrderPriceTotal ,
       SUM(a.orderqty*a.orderprice) AS OrderTotal ,
       a.orderdesc ,
       MAX(a.orderdate) AS LatestOrderDate ,
       MAX(b.orderstatus) AS LatestOrderStatus ,
       MAX(b.orderreviewstatus) AS LatestOrderViewStatus 
FROM #first a INNER JOIN #second b ON a.userid = b.userid 
GROUP BY a.userid, a.orderdesc