Code666 (代码贴、代码片段)

[SQL] 按某一字段分组取最大(小)值所在行的数据 →→→→→进入此内容的聊天室

来自 , 2020-08-11, 写在 SQL, 查看 127 次.
URL http://www.code666.cn/view/a322852c
  1. /*
  2. 数据如下：
  3. name val memo
  4. a 2 a2(a的第二个值)
  5. a 1 a1--a的第一个值
  6. a 3 a3:a的第三个值
  7. b 1 b1--b的第一个值
  8. b 3 b3:b的第三个值
  9. b 2 b2b2b2b2
  10. b 4 b4b4
  11. b 5 b5b5b5b5b5
  12. */
  13. --创建表并插入数据：
  14. CREATE TABLE tb(name VARCHAR(10),val INT,memo VARCHAR(20))
  15. INSERT INTO tb VALUES('a', 2, 'a2(a的第二个值)')
  16. INSERT INTO tb VALUES('a', 1, 'a1--a的第一个值')
  17. INSERT INTO tb VALUES('a', 3, 'a3:a的第三个值')
  18. INSERT INTO tb VALUES('b', 1, 'b1--b的第一个值')
  19. INSERT INTO tb VALUES('b', 3, 'b3:b的第三个值')
  20. INSERT INTO tb VALUES('b', 2, 'b2b2b2b2')
  21. INSERT INTO tb VALUES('b', 4, 'b4b4')
  22. INSERT INTO tb VALUES('b', 5, 'b5b5b5b5b5')
  23. GO
  24.  
  25. --一、按name分组取val最大的值所在行的数据。
  26. --方法1：
  27. SELECT a.* FROM tb a WHERE val = (SELECT MAX(val) FROM tb WHERE name = a.name) ORDER BY a.name
  28. --方法2：
  29. SELECT a.* FROM tb a WHERE NOT EXISTS(SELECT 1 FROM tb WHERE name = a.name AND val > a.val)
  30. --方法3：
  31. SELECT a.* FROM tb a,(SELECT name,MAX(val) val FROM tb GROUP BY name) b WHERE a.name = b.name AND a.val = b.val ORDER BY a.name
  32. --方法4：
  33. SELECT a.* FROM tb a INNER JOIN (SELECT name , MAX(val) val FROM tb GROUP BY name) b ON a.name = b.name AND a.val = b.val ORDER BY a.name
  34. --方法5
  35. SELECT a.* FROM tb a WHERE 1 > (SELECT COUNT(*) FROM tb WHERE name = a.name AND val > a.val ) ORDER BY a.name
  36. /*
  37. name val memo
  38. ---------- ----------- --------------------
  39. a 3 a3:a的第三个值
  40. b 5 b5b5b5b5b5
  41. */
  42.  
  43. --二、按name分组取val最小的值所在行的数据。
  44. --方法1：
  45. SELECT a.* FROM tb a WHERE val = (SELECT MIN(val) FROM tb WHERE name = a.name) ORDER BY a.name
  46. --方法2：
  47. SELECT a.* FROM tb a WHERE NOT EXISTS(SELECT 1 FROM tb WHERE name = a.name AND val < a.val)
  48. --方法3：
  49. SELECT a.* FROM tb a,(SELECT name,MIN(val) val FROM tb GROUP BY name) b WHERE a.name = b.name AND a.val = b.val ORDER BY a.name
  50. --方法4：
  51. SELECT a.* FROM tb a INNER JOIN (SELECT name , MIN(val) val FROM tb GROUP BY name) b ON a.name = b.name AND a.val = b.val ORDER BY a.name
  52. --方法5
  53. SELECT a.* FROM tb a WHERE 1 > (SELECT COUNT(*) FROM tb WHERE name = a.name AND val < a.val) ORDER BY a.name
  54. /*
  55. name val memo
  56. ---------- ----------- --------------------
  57. a 1 a1--a的第一个值
  58. b 1 b1--b的第一个值
  59. */
  60.  
  61. --三、按name分组取第一次出现的行所在的数据。
  62. SELECT a.* FROM tb a WHERE val = (SELECT top 1 val FROM tb WHERE name = a.name) ORDER BY a.name
  63. /*
  64. name val memo
  65. ---------- ----------- --------------------
  66. a 2 a2(a的第二个值)
  67. b 1 b1--b的第一个值
  68. */
  69.  
  70. --四、按name分组随机取一条数据。
  71. SELECT a.* FROM tb a WHERE val = (SELECT top 1 val FROM tb WHERE name = a.name ORDER BY newid()) ORDER BY a.name
  72. /*
  73. name val memo
  74. ---------- ----------- --------------------
  75. a 1 a1--a的第一个值
  76. b 5 b5b5b5b5b5
  77. */
  78.  
  79. --五、按name分组取最小的两个(N个)val
  80. SELECT a.* FROM tb a WHERE 2 > (SELECT COUNT(*) FROM tb WHERE name = a.name AND val < a.val ) ORDER BY a.name,a.val
  81. SELECT a.* FROM tb a WHERE val IN (SELECT top 2 val FROM tb WHERE name=a.name ORDER BY val) ORDER BY a.name,a.val
  82. SELECT a.* FROM tb a WHERE EXISTS (SELECT COUNT(*) FROM tb WHERE name = a.name AND val < a.val HAVING COUNT(*) < 2) ORDER BY a.name
  83. /*
  84. name val memo
  85. ---------- ----------- --------------------
  86. a 1 a1--a的第一个值
  87. a 2 a2(a的第二个值)
  88. b 1 b1--b的第一个值
  89. b 2 b2b2b2b2
  90. */
  91.  
  92. --六、按name分组取最大的两个(N个)val
  93. SELECT a.* FROM tb a WHERE 2 > (SELECT COUNT(*) FROM tb WHERE name = a.name AND val > a.val ) ORDER BY a.name,a.val
  94. SELECT a.* FROM tb a WHERE val IN (SELECT top 2 val FROM tb WHERE name=a.name ORDER BY val DESC) ORDER BY a.name,a.val
  95. SELECT a.* FROM tb a WHERE EXISTS (SELECT COUNT(*) FROM tb WHERE name = a.name AND val > a.val HAVING COUNT(*) < 2) ORDER BY a.name
  96. /*
  97. name val memo
  98. ---------- ----------- --------------------
  99. a 2 a2(a的第二个值)
  100. a 3 a3:a的第三个值
  101. b 4 b4b4
  102. b 5 b5b5b5b5b5
  103. */
  104. --七，如果整行数据有重复，所有的列都相同。
  105. /*
  106. 数据如下：
  107. name val memo
  108. a 2 a2(a的第二个值)
  109. a 1 a1--a的第一个值
  110. a 1 a1--a的第一个值
  111. a 3 a3:a的第三个值
  112. a 3 a3:a的第三个值
  113. b 1 b1--b的第一个值
  114. b 3 b3:b的第三个值
  115. b 2 b2b2b2b2
  116. b 4 b4b4
  117. b 5 b5b5b5b5b5
  118. */
  119. --在sql server 2000中只能用一个临时表来解决，生成一个自增列，先对val取最大或最小，然后再通过自增列来取数据。
  120. --创建表并插入数据：
  121. CREATE TABLE tb(name VARCHAR(10),val INT,memo VARCHAR(20))
  122. INSERT INTO tb VALUES('a', 2, 'a2(a的第二个值)')
  123. INSERT INTO tb VALUES('a', 1, 'a1--a的第一个值')
  124. INSERT INTO tb VALUES('a', 1, 'a1--a的第一个值')
  125. INSERT INTO tb VALUES('a', 3, 'a3:a的第三个值')
  126. INSERT INTO tb VALUES('a', 3, 'a3:a的第三个值')
  127. INSERT INTO tb VALUES('b', 1, 'b1--b的第一个值')
  128. INSERT INTO tb VALUES('b', 3, 'b3:b的第三个值')
  129. INSERT INTO tb VALUES('b', 2, 'b2b2b2b2')
  130. INSERT INTO tb VALUES('b', 4, 'b4b4')
  131. INSERT INTO tb VALUES('b', 5, 'b5b5b5b5b5')
  132. GO
  133.  
  134. SELECT * , px = IDENTITY(INT,1,1) INTO tmp FROM tb
  135.  
  136. SELECT m.name,m.val,m.memo FROM
  137. (
  138. SELECT t.* FROM tmp t WHERE val = (SELECT MIN(val) FROM tmp WHERE name = t.name)
  139. ) m WHERE px = (SELECT MIN(px) FROM
  140. (
  141. SELECT t.* FROM tmp t WHERE val = (SELECT MIN(val) FROM tmp WHERE name = t.name)
  142. ) n WHERE n.name = m.name)
  143.  
  144. DROP TABLE tb,tmp
  145.  
  146. /*
  147. name val memo
  148. ---------- ----------- --------------------
  149. a 1 a1--a的第一个值
  150. b 1 b1--b的第一个值
  151.  
  152. (2 行受影响)
  153. */
  154. --在sql server 2005中可以使用row_number函数，不需要使用临时表。
  155. --创建表并插入数据：
  156. CREATE TABLE tb(name VARCHAR(10),val INT,memo VARCHAR(20))
  157. INSERT INTO tb VALUES('a', 2, 'a2(a的第二个值)')
  158. INSERT INTO tb VALUES('a', 1, 'a1--a的第一个值')
  159. INSERT INTO tb VALUES('a', 1, 'a1--a的第一个值')
  160. INSERT INTO tb VALUES('a', 3, 'a3:a的第三个值')
  161. INSERT INTO tb VALUES('a', 3, 'a3:a的第三个值')
  162. INSERT INTO tb VALUES('b', 1, 'b1--b的第一个值')
  163. INSERT INTO tb VALUES('b', 3, 'b3:b的第三个值')
  164. INSERT INTO tb VALUES('b', 2, 'b2b2b2b2')
  165. INSERT INTO tb VALUES('b', 4, 'b4b4')
  166. INSERT INTO tb VALUES('b', 5, 'b5b5b5b5b5')
  167. GO
  168.  
  169. SELECT m.name,m.val,m.memo FROM
  170. (
  171. SELECT * , px = ROW_NUMBER() OVER(ORDER BY name , val) FROM tb
  172. ) m WHERE px = (SELECT MIN(px) FROM
  173. (
  174. SELECT * , px = ROW_NUMBER() OVER(ORDER BY name , val) FROM tb
  175. ) n WHERE n.name = m.name)
  176.  
  177. DROP TABLE tb
  178.  
  179. /*
  180. name val memo
  181. ---------- ----------- --------------------
  182. a 1 a1--a的第一个值
  183. b 1 b1--b的第一个值
  184.  
  185. (2 行受影响)
  186. */
  187.  
  188. //SQL/1210

回复 "按某一字段分组取最大(小)值所在行的数据"

这儿你可以回复上面这条便签

captcha

贴代码片段 - Code666