SELECT orderID,
foodID,
userID,
orderDate,
restaurantName,
foodName,
price,
amount,
category
FROM OurWebProfile.dbo.tb_OrderInfo
WHERE userID='zyp' --第一遍
UNION ALL
SELECT 0 AS orderID,
NULL AS foodID,
NULL AS userID,
NULL AS orderDate,
NULL AS restaurantName,
NULL AS foodName,
SUM(price) AS price,
SUM(amount) AS amount,
NULL AS category
FROM OurWebProfile.dbo.tb_OrderInfo
WHERE userID='zyp' --第二遍(而且两遍WHERE一样)
foodID,
userID,
orderDate,
restaurantName,
foodName,
price,
amount,
category
FROM OurWebProfile.dbo.tb_OrderInfo
WHERE userID='zyp' --第一遍
UNION ALL
SELECT 0 AS orderID,
NULL AS foodID,
NULL AS userID,
NULL AS orderDate,
NULL AS restaurantName,
NULL AS foodName,
SUM(price) AS price,
SUM(amount) AS amount,
NULL AS category
FROM OurWebProfile.dbo.tb_OrderInfo
WHERE userID='zyp' --第二遍(而且两遍WHERE一样)
select * from (... union ...)t where userID='zyp'
from(
SELECT orderID,
foodID,
userID,
orderDate,
restaurantName,
foodName,
price,
amount,
category
FROM OurWebProfile.dbo.tb_OrderInfo
UNION ALL
SELECT 0 AS orderID,
NULL AS foodID,
NULL AS userID,
NULL AS orderDate,
NULL AS restaurantName,
NULL AS foodName,
SUM(price) AS price,
SUM(amount) AS amount,
NULL AS category
FROM OurWebProfile.dbo.tb_OrderInfo
) t
WHERE userID='zyp'
select * from (
SELECT orderID,
foodID,
userID,
orderDate,
restaurantName,
foodName,
price,
amount,
category
FROM OurWebProfile.dbo.tb_OrderInfo UNION ALL
SELECT 0 AS orderID,
NULL AS foodID,
NULL AS userID,
NULL AS orderDate,
NULL AS restaurantName,
NULL AS foodName,
SUM(price) AS price,
SUM(amount) AS amount,
NULL AS category
FROM OurWebProfile.dbo.tb_OrderInfo ) A
WHERE userID='zyp'