1、语法:
SELECT
*
FROM
(
SELECT
t.*,ROW_NUMBER () OVER (PARTITION BY 分组列 ORDER BY 排序列 DESC ) rn
FROM
查询的表格
)
WHERE
rn = 1;
2、举例
数据库中有一个表wwwhhh格数据如下:
billno | billcode | billsn | goodsid | goodsname | jgcode |
1001 | abc001 | 1 | yp001 | 感冒颗粒 | 12345 |
1001 | abc001 | 2 | yp002 | 感冒冲剂 | 12346 |
1001 | abc001 | 2 | yp002 | 板蓝根 | 12347 |
1002 | abc002 | 1 | yp003 | 三九胃泰 | 12348 |
1002 | abc002 | 2 | yp007 | 布洛芬胶囊 | 12349 |
1002 | abc002 | 3 | yp004 | 杯子 | 12344 |
1002 | abc002 | 3 | yp004 | 杯子 | 12343 |
1002 | abc002 | 4 | yp005 | 毛巾 | 12342 |
1002 | abc002 | 5 | yp006 | 眼镜 | 12341 |
上述数据第二行第三行除了jgcode 其他数据均相同,我想通过查询去掉冗余项,像第二行和第三行这类数据只取一个,但又想取所有数据列。但是如通过group by 或者 distinct又查不到最后一列。
用上述语法代码如下:
SELECT
*
FROM
(
SELECT
t.*,
ROW_NUMBER () OVER (PARTITION BY billno,billsn ORDER BY jgcode DESC) rn
FROM
wwwhhh t
)
WHERE
rn = 1;
执行后的结果:
billno | billcode | billsn | goodsid | goodsname | jgcode |
1001 | abc001 | 1 | yp001 | 感冒颗粒 | 12345 |
1001 | abc001 | 2 | yp002 | 感冒冲剂 | 12346 |
1002 | abc002 | 1 | yp003 | 三九胃泰 | 12348 |
1002 | abc002 | 2 | yp007 | 布洛芬胶囊 | 12349 |
1002 | abc002 | 3 | yp004 | 杯子 | 12344 |
1002 | abc002 | 4 | yp005 | 毛巾 | 12342 |
1002 | abc002 | 5 | yp006 | 眼镜 | 12341 |