sql查询中怎么将两条内容相同的记录显示成一条？ SQL 如何连接同一表中两条记录信息成一条记录
sql\u67e5\u8be2\u4e2d\u600e\u4e48\u5c06\u4e24\u6761\u5185\u5bb9\u76f8\u540c\u7684\u8bb0\u5f55\u663e\u793a\u6210\u4e00\u6761sql\u67e5\u8be2\u4e2d\u4e24\u6761\u5185\u5bb9\u76f8\u540c\u7684\u8bb0\u5f55\u663e\u793a\u6210\u4e00\u6761\u53ef\u4ee5\u7528group by\u8bed\u53e5\u6216distinct\u8bed\u53e5\u6765\u5b9e\u73b0\u3002
\u5982\uff0ctest\u8868\u4e2d\u6709\u5982\u4e0b\u6570\u636e\uff1a
group by\u7684\u65b9\u6cd5\uff1a
select id,name from test group by id,name;\u67e5\u8be2\u7ed3\u679c\uff1a
distinct\u7684\u65b9\u6cd5\uff1a
select distinct id,name from test;\u67e5\u8be2\u7ed3\u679c\uff1a
'\u53ef\u4ee5\u7406\u89e3\u4e3a\u4f60\u7684\u6240\u6709\u5b57\u6bb5\u6570\u636e\u5e76\u4e0d\u662f\u552f\u4e00\u7684\uff0c\u4f46\u4f60\u7684Visit_No\u503c\u7edd\u5bf9\u662f\u4ece\u5c0f\u5230\u5927\u7684'\u8fd9\u662f\u4f60\u5f53\u524d\u67e5\u8be2\u91cd\u5e86\u7684\u4ee3\u7801Sql = "Select T.Bus_No,T.Schedule_Type,T.Station,T.Visit_No,T.M_CALL,T.ETA from tableT where T.Station='chongqing'"' \u5c06\u67e5\u8be2\u91cd\u5e86\u7684Visit_No\u503c\u8d4b\u7ed9VisitAVisitA = ""'\u6839\u636eVisitA\u67e5\u8be2\u51fa\u6bd4\u5b83\u5c0f\u7684\u503c\uff0c\u518d\u4e0e\u5f53\u524d\u503c\u8fdb\u884c\u6bd4\u8f83\u627e\u5230\u4e0a\u4e00\u6761\u503cSql = "Select T.Station,T.Visit_No,T.ETD from TableT where T.Visit_No<'" & VisitA & "'order by T.Visit_No asc"i = 1Do While Not b.EOF '\u5faa\u73af\u6bd4\u5bf9\u6570\u636e\uff0c\u627e\u5230\u4e0a\u4e00\u6761\u6570\u636e If i = 1 Then Station = b(1) Price = b(2) ETD = b(3) ElseIf Price <= b(2) Then Station = b(1) Price = b(2) ETD = b(3) End If i = i + 1b.MoveNextLoop'\u6700\u540e\u5c06\u7ed3\u679c\u5199\u5230\u540e\u9762\u5c31\u53ef\u4ee5\u4e86
sql查询中两条内容相同的记录显示成一条可以用group by语句或distinct语句来实现。distinct支持单列、多列的去重方式。单列去重的方式简明易懂，即相同值只保留1个。多列的去重则是根据指定的去重的列信息来进行，即只有所有指定的列信息都相同，才会被认为是重复的信息。
示例数据表中的数据：mysql> select * from talk_test;
+----+-------+--------+| id | name | mobile |
+----+-------+--------+| 1 | xiao9 | 555555 |
| 2 | xiao6 | 666666 |
| 3 | xiao9 | 888888 |
| 4 | xiao9 | 555555 || 5 | xiao6 | 777777 |
+进行单列去重后的结果：mysql> select distinct(name) from talk_test;
+-------+| name |
+-------+| xiao9 || xiao6 |
+-------+2 rows in set (0.01 sec)mysql> select distinct(mobile) from talk_test;
+--------+| mobile |
+--------+| 555555 |
| 666666 |
| 888888 || 777777 |
只会保留指定的列的信息进行多列去重后的结果：
mysql> select distinct name,mobile from talk_test;
+-------+--------+| name | mobile |
+-------+--------+| xiao9 | 555555 |
| xiao6 | 666666 |
| xiao9 | 888888 || xiao6 | 777777 |
+-------+--------+**只有所有指定的列信息都相同，才会被认定为重复的信息
group by使用的频率相对较高，但正如其功能一样，它的目的是用来进行聚合统计的，虽然也可能实现去重的功能，但这并不是它的长项。
绛旓細select * from 琛ㄥ悕 where 瀛楁1=瀛楁2 delete 琛ㄥ悕 where 瀛楁1=瀛楁2 --- 鏈绠鍗曠殑鏂规硶 鏂板缓涓涓〃.琛ㄥ瓧娈典笌鐜板湪杩欎釜瀹屽叏鐩稿悓.鎶婃湁閲嶅瀛楁鐨勫垪璁剧疆涓轰富閿苟閫夋嫨蹇界暐閲嶅閿夐」(鐢ㄤ紒涓氱鐞嗗櫒)鐒跺悗鎶婅〃涓殑鍐呭鍓垏鍒版柊琛閲鍐嶅鍒跺洖鏉ュ氨鍘绘帀閲嶅閿簡....
绛旓細select * from 琛 where 鍒1=鍒2 鎴栬卻elect * from A A1 INNER JOIN A A2 ON A1.鍒1=A2.鍒2
绛旓細select * from table1 a where a.rowid!=(select max(rowid) from table1 b where a.绗竴涓瓧娈=b.绗竴涓瓧娈 and a.绗簩涓瓧娈=b.绗簩涓瓧娈);鍚庤竟鐨刟.id=b.id 鍙互鍒楀嚭澶氫釜鏉ワ紝濡傛灉鎵惧畬鍏鐩稿悓鐨涓ゆ潯璁板綍鍒欐槸a鐨勬瘡涓瓧娈甸兘涓巄鐨勬瘡涓瓧娈鐩哥瓑 ...
绛旓細瀛楁鍊煎幓鎺夌┖鏍煎拰杞崲鎴愬ぇ鍐欐垨灏忓啓涔嬪悗锛屽啀鍋氭瘮杈冨氨鍙互浜嗐備笉鐭ラ亾浣犳槸浠涔堟暟鎹簱锛屼互ORACLE鏁版嵁搴撲妇渚嬶細SELECT DISTINCT IN1.* FROM INVENTORY IN1WHERE EXISTS( SELECT IN2.* FROM INVENTORY IN2 WHERE IN1.ID != IN2.ID AND LOWER(REPLACE(IN1.NAME, ' ')) = LOWER(REPLACE...
绛旓細'test_col_2'tbl_name,count(*)from test_col_2 t 4銆佽〃1鏈夐儴鍒嗘瘮琛2澶氱殑鏁版嵁锛宻elect from test_col_1 minus select from test_col_2;5銆佹彃鍏ヨ〃1澶氱殑鏁版嵁锛屽琛2锛屾墽琛sql锛屽彲浠ュ彂鐜版湁澶氭潯璁板綍鎻掑叆銆俰nsert into test_col_2 select from test_col_1 minus select from test_col_2;
绛旓細--- 鎴戣繖涓猻ql鏄病鏈夐棶棰樼殑 濡傛灉姝绘帀鐨勫師鍥犲彲鑳 1浣犵殑鏈哄櫒闂锛岄噸鏂板惎鍔╯ql鎵ц杩欎釜璇彞鐪嬩細涓嶄細鏈夐棶棰 2鍙兘鏄綘鍦鏌ヨ杩欎釜sql涔嬪墠锛屾湁淇敼琛ㄦ暟鎹鐨剆ql鎵ц锛岄犳垚琛ㄨ閿佷綇浜嗐--- 瑙ｅ喅鍔炴硶 1 鍏抽棴sql鏈嶅姟锛岀劧鍚庨噸鏂板惎鍔紝鐩存帴鍦ㄦ煡璇㈠垎鏋愬櫒閲屾墽琛岃繖涓鍙 2 a,b,瀛楁鍚1 杩欎簺 鏇挎崲鎴愪綘瀹為檯鐨...
绛旓細妤间笂璇寸殑瀵 鐢ㄥ祵濂楀氨鍙互 select 璇剧▼鍙 from 璇剧▼淇℃伅琛 group by 涓婅鏃堕棿,涓婅鑺傛暟 where 璇剧▼鍙 in (select 璇剧▼鍙 from 閫夎琛 where 瀛﹀彿=123)杩欐牱鍐欐垜鎰熻搴旇鏄彲浠ョ殑 涓嶈繃鍙兘鏈夐棶棰 鍥犱负group by鐨勫彲鑳芥棤娉曟崱鍑鸿绋嬪彿 杩欎篃寰楁牴鎹叿浣撶殑鏁版嵁搴撶郴缁熸潵璇 浣犲彲浠ヨ瘯涓涓 瑕佹槸涓嶈鍐嶈瘯璇...
绛旓細鍒嗘瀽锛1銆侀鍏堝緱鍑轰袱涓〃鐨勫苟闆 娉細full join :瀛樺湪鍖归厤锛屽尮閰嶆樉绀猴紱鍚屾椂锛屽皢鍚勪釜琛ㄤ腑涓嶅尮閰嶇殑鏁版嵁涓庣┖鏁版嵁琛屽尮閰嶈繘琛屾樉绀恒傚彲浠ョ湅鎴愭槸宸﹀杩炴帴涓庡彸澶栬繛鎺ョ殑骞堕泦銆傚浘涓粨鏋滃乏渚т袱鍒椾负TABLE1锛屽彸渚т袱鍒椾负TABLE2銆傚墠涓夋潯璁板綍琛ㄧずTABLE1鍜孴ABLE2閮芥湁鐨勬暟鎹俆ABLE1椤逛负NULL鐨勮褰曡鏄嶵ABLE2涓棤鐩稿悓椤...
绛旓細鏌ヨ涓寮犺〃涓殑鏁版嵁鍙互閲囧彇杩炴帴鍜岃仈鍚堢殑鏂规硶鏉ュ悎骞躲佺粍鍚堟潵鑷笉鍚岃〃閲岀殑鏁版嵁 锛屽叾涓繛鎺ュ張鍙互鏈夊唴杩炴帴銆佸杩炴帴鍜岃嚜鐒惰繛鎺ョ瓑澶氱褰㈠紡锛岃繛鎺ユ潯浠跺彲浠ユ牴鎹渶瑕佷换鏄撹缃紝浜﹀彲浠ュ绛夎繛鎺ヤ篃鍙互闈炲绛夎繛鎺ワ紝杩樺彲浠ョ粍鍚堜娇鐢ㄩ昏緫杩愮畻绗﹁缃繛鎺ユ潯浠躲傚叿浣鐨凷QL璇彞蹇呴』鎹変緵琛ㄧ粨鏋勫拰杈撳嚭瑕佹眰鎵嶈兘缁欏嚭锛岃鍙ュ舰寮忕殑...
绛旓細杩欎釜鏄煡鎵鏈夎褰 select * from 琛 where 瀛楁鍚=瀛楁鍚 涓嬮潰杩欎釜鏄煡璁板綍鐨勬暟閲忥紙鏉℃暟锛塻elect count(*) from琛 where 瀛楁鍚=瀛楁鍚
