22,206
社区成员
发帖
与我相关
我的任务
分享
SELECT * from (
SELECT ROW_NUMBER() over(order by AuthorizedCode Desc) as rowId,MedicineId,NameCN,AuthorizedCode from T_Medicine
WHERE AuthorizedCode IN (
SELECT AuthorizedCode FROM(
SELECT AuthorizedCode,NameCN FROM dbo.Medicine WHERE AuthorizedCode!='' GROUP BY AuthorizedCode,NameCN
) tmp GROUP BY AuthorizedCode HAVING COUNT(*) > 1)) as t
SELECT ROW_NUMBER() over(order by AuthorizedCode Desc) as rowId,MedicineId,NameCN,AuthorizedCode from T_Medicine
WHERE rowId between 20000 and 20020
表 'Medicine'。扫描计数 15,逻辑读取 25805 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'MedicineCategory'。扫描计数 0,逻辑读取 96 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(23 行受影响)
SELECT *
FROM (SELECT Row_number() OVER(ORDER BY a.authorizedcode DESC) AS rowid,
MedicineId,MillId,NameCN,AliasCN,a.AuthorizedCode,StandardCodeNo,InputAccountName,SerialCode,Standard,TrocheType,IsMedicare,IsPrescription,NamePath
FROM view_medicine a RIGHT JOIN
(SELECT authorizedcode FROM (SELECT authorizedcode, namecn
FROM dbo.medicine WHERE active = 1 AND authorizedcode IS NOT NULL
AND authorizedcode != '' GROUP BY authorizedcode,namecn) tmp
GROUP BY authorizedcode HAVING COUNT(*) > 1) AS b
ON a.authorizedcode = b.authorizedcode
WHERE a.active = 1
) AS t
WHERE rowid BETWEEN 21 AND 40
SELECT * from
(
SELECT ROW_NUMBER() over(order by AuthorizedCode Desc) as rowId,MedicineId,NameCN,AuthorizedCode
from T_Medicine
WHERE AuthorizedCode IN
(
SELECT AuthorizedCode,NameCN
FROM dbo.Medicine
WHERE AuthorizedCode!=''
GROUP BY AuthorizedCode,NameCN
having count(AuthorizedCode)>1
)
) as t
表 'T_Medicine'。扫描计数 208920,逻辑读取 1998404 次,物理读取 0 次,预读 12 次,lob 逻辑读取 0
次,lob 物理读取 0 次,lob 预读 0 次。