單表查詢 獲取出一個患者至少在兩家店購買過的資料，不滿足的都排除
表欄位：
藥店編碼 藥店名稱 購藥時間 月份 藥品名稱 規格 購買數量 藥店省份 藥店城市 處方醫院 患者編號（隨機）歸屬藥店
查詢全表的查詢陳述句如下：
SELECT
cdd.SELLER_CODE AS 'sellerCode',
cdd.SELLER_NAME AS 'sellerName',
cdd.ORDER_TIME AS 'orderTime',
QUARTER(cdd.ORDER_TIME) AS 'yearQuarter',
cdd.MED_NAME AS 'medName',
cdd.SPEC AS 'spec',
cdd.AMOUNT AS 'amount',
cdd.PROVINCE_NAME AS 'provinceName',
cdd.CITY_NAME AS 'cityName',
cdd.HOSPITAL AS 'hospital',
cdd.PATIENT_CODE AS 'patientCode',
cdd.SELLER_CODE AS 'lastSellerCode'
FROM
yiyao_b2c.cmd_desensitization_data cdd
請問各位大佬，怎樣撰寫：
還有每一個用戶的歸屬藥店都是最后一次購藥藥店
uj5u.com熱心網友回復：
SELECT *
FROM yiyao_b2c.cmd_desensitization_data cdd
WHERE EXISTS (SELECT 1 FROM yiyao_b2c.cmd_desensitization_data WHERE cdd.藥店編碼<>藥店編碼 AND cdd.患者編號=患者編號)
uj5u.com熱心網友回復：
非常感謝大佬麻煩在問下您，查詢出來的資料 歸屬藥店都查詢為 末次訂單 sellerCode(門店編碼）
uj5u.com熱心網友回復：
SLECT A.*,B.藥店編碼 AS 歸屬藥店_NEW
FROM yiyao_b2c.cmd_desensitization_data AS A
JOIN
(SELECT 患者編號,藥店編碼
FROM yiyao_b2c.cmd_desensitization_data cdd
WHERE NOT EXISTS (SELECT 1 FROM yiyao_b2c.cmd_desensitization_data WHERE cdd.患者編號=患者編號 AND 購藥時間>cdd.購藥時間)
AND 藥店編碼=歸屬藥店) AS B ON A.患者編號=B.患者編號
uj5u.com熱心網友回復：
SELECT
A.*,
B.SELLER_CODE AS 'newSellerCode'
FROM
yiyao_b2c.cmd_desensitization_data AS A
JOIN (
SELECT
PATIENT_CODE,
SELLER_CODE
FROM
yiyao_b2c.cmd_desensitization_data cdd
WHERE
NOT EXISTS ( SELECT 1 FROM yiyao_b2c.cmd_desensitization_data WHERE cdd.PATIENT_CODE = PATIENT_CODE AND ORDER_TIME > cdd.ORDER_TIME )
) AS B ON A.PATIENT_CODE = B.PATIENT_CODE
大佬有問題啊，這樣子資料都查出來了
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/248785.html
標籤：基礎類