sql-server - SQL Server 返回不同的行?
问题描述
我正在运行这个 SQL 语句:
SELECT
s.indx, S.custid, S.date, S.qty, S.item, S.price, S.extprice,
S.salestax, S.linetotal, S.salenbr, C.company, P.MOP
FROM
sales S
JOIN
cust C ON S.custid = C.custid
JOIN
pmts P ON S.salenbr = p.salenbr
WHERE
(s.salenbr = 16749)
它返回这个结果集:
indx custid date qty item price extprice salestax linetotal salenbr company MOP
170835 695 2021-09-27 10:00:44.000 1.00 1X4X12 7.85 7.85 0.75 8.60 16749 COUNTER SALE CS
170835 695 2021-09-27 10:00:44.000 1.00 1X4X12 7.85 7.85 0.75 8.60 16749 COUNTER SALE CC
170836 695 2021-09-27 10:00:44.000 1.00 1X6X12 11.62 11.62 1.10 12.72 16749 COUNTER SALE CS
170836 695 2021-09-27 10:00:44.000 1.00 1X6X12 11.62 11.62 1.10 12.72 16749 COUNTER SALE CC
我只想拉出付款方式“MOP”不同的行。我正在使用数据来运行报告,并且只需要具有不同或独特的 MOP 的数据。
谢谢你
解决方案
您可以ROW_NUMBER
在此处使用任意MOP
顺序从每个组中获取“第一条”记录:
WITH cte AS (
SELECT s.indx, S.custid, S.date, S.qty, S.item, S.price, S.extprice,
S.salestax, S.linetotal, S.salenbr, C.company, P.MOP,
ROW_NUMBER() OVER (PARTITION BY P.MOP ORDER BY S.date) rn
FROM sales S
INNER JOIN cust C ON S.custid = C.custid
INNER JOIN pmts P ON S.salenbr = P.salenbr
WHERE S.salenbr = 16749
)
SELECT indx, custid, date, qty, item, price, exitprice,
salestax, linetotal, salenbr, company, MOP
FROM cte
WHERE rn = 1;
推荐阅读
- python - Python vaex 如何从 CSV 文件创建数据框?
- c# - Microsoft Graph API 守护程序 - 错误:ResourceNotFound 消息:无法发现资源
- prometheus - Prometheus 报告 node_memory_Active_bytes 和 free -b 的值不同
- graphics - 通过代码建立一个更大的显示缓冲区
- silverstripe - How to override the existing method of an existing class, FlysystemAssetStore that is part of the framework in SilverStripe
- c - segmentation fault (core dumped) error in a c program for combination function
- ember.js - Ember.Js Using TransitionTo Route with dynamic segment
- java - 应用程序在第二次启动时崩溃,不会将密钥保留在内存中
- python - Python count list's values and put them in ordered list without library
- julia - How to not shadow variable in inner block in Julia?