Table a
| id | name |
| 1 | 小明 |
| 2 | 小玉 |
Table b
| id | a_id | buy_prod |
| 1 | 1 | 空拍機 |
| 2 | 1 | 小折 |
| 3 | 1 | 東京 |
| 4 | 2 | 相印機 |
| 5 | 2 | 房子 |
1.取得小明和小玉各有多少個想買的商品
MySQL：
SELECT name, COUNT( a_id )
FROM a
LEFT JOIN b ON a.id = b.a_id
GROUP BY a.id
MSSQL：
因為SELECT的欄位一定要在GROUP BY中，
所以用MySQL的寫法會錯。
SELECT a.name,b.count
FROM a
LEFT JOIN (
SELECT a_id,COUNT( a_id ) as count
FROM a
LEFT JOIN b ON a.id = b.a_id
GROUP BY a_id
) b ON a.id=b.a_id
MSSQL其他去除重複的方法
1.
Select * From [Product] Where ID In (Select Max(ID) From [Product] Group By 產品名稱)
select * from hoursell WHERE hourid IN (SELECT Max(hourid) FROM hoursell GROUP BY art_no)
2.
with temp as( SELECT *, ROW_NUMBER() over(order by hourid) as rnk FROM hoursell ) select * from temp WHERE rnk IN (SELECT Max(rnk) FROM temp GROUP BY art_no)
3.
with tmp as (
select * from hoursell
WHERE hourid IN (SELECT Max(hourid) FROM hoursell GROUP BY art_no)
)
SELECT MEMO.ART_NO
FROM WEBARTMEMO MEMO
LEFT JOIN tmp hs ON MEMO.ART_NO=hs.art_no
WHERE MEMO.on_time <= '20160407150933' AND MEMO.off_time >= '20160407150933'
AND MEMO.stock > 0 AND MEMO.stock > safe_stock AND isnull(MEMO.WEBKIND_NO,'') != ''
AND isnull(no_update_out,'') != '' AND MEMO.is_del='N'
4.
WITH pa as
( SELECT o.* ,h.start_time,h.end_time,ROW_NUMBER()
OVER(ORDER BY o.post_time DESC ,o.ART_NO ASC) as sort,
maori = CASE WHEN h.start_time <= '20160407033208' AND
h.end_time >= '20160407033208' THEN o.hour_price - CEILING(o.CURRMAVG * 1.05)
WHEN o.BDATE <= '20160407' AND
o.EDATE >= '20160407' THEN o.SPA_IN - CEILING(o.CURRMAVG * 1.05)
ELSE o.CURRSIN - CEILING(o.CURRMAVG * 1.05) END
FROM WEBARTMEMO o
LEFT JOIN (select * from hoursell WHERE hourid IN
(SELECT Max(hourid) FROM hoursell GROUP BY art_no)) h ON o.ART_NO = h.art_no
WHERE o.ART_NO IS NOT NULL AND o.on_time <= '20160407153208'
AND o.off_time >= '20160407153208' AND o.stock > 0 AND o.stock > safe_stock
AND isnull(o.WEBKIND_NO,'') != '' AND isnull(no_update_out,'') != '' AND o.is_del='N' )
SELECT * FROM pa WHERE sort BETWEEN 1 and 20