sql兩表比較

⑴ 鍦⊿QLServer涓濡備綍姣旇緝涓や釜琛ㄧ殑鍚勭粍鏁版嵁

寮濮 鍓嶄竴闃靛瓙錛屽湪欏圭洰涓紕板埌榪欐牱涓涓猄QL鏌ヨ㈤渶奼傦紝鏈変袱涓鐩稿悓緇撴瀯鐨勮〃(table_left table_right)錛屽備笅錛 鍥1. 媯鏌ヨ〃table_left鐨勫悇緇(groupId)錛屾槸鍚﹀湪琛╰able_right涓瀛樺湪鏈変竴緇(groupId)鏁版嵁(data)涓庡畠鐨勬暟鎹(data)瀹屽叏鐩哥瓑. 濡傚浘1. 鍙浠ョ湅鍑鴻〃table_lef




寮濮

鍓嶄竴闃靛瓙錛屽湪欏圭洰涓紕板埌榪欐牱涓涓猄QL鏌ヨ㈤渶奼傦紝鏈変袱涓鐩稿悓緇撴瀯鐨勮〃(table_left & table_right)錛屽備笅錛



鍥1.

媯鏌ヨ〃table_left鐨勫悇緇(groupId)錛屾槸鍚﹀湪琛╰able_right涓瀛樺湪鏈変竴緇(groupId)鏁版嵁(data)涓庡畠鐨勬暟鎹(data)瀹屽叏鐩哥瓑.

濡傚浘1. 鍙浠ョ湅鍑鴻〃table_left鍜宼able_right瀛樺湪涓ょ粍鏁版嵁瀹屾暣鐩哥瓑錛



鍥2.

鍒嗘瀽

浠庝笂闈㈢殑涓や釜琛錛屽彲浠ョ煡閬撳畠浠瀛樻斁鐨勬槸涓緇勪竴緇勭殑鏁版嵁錛涢偅涔堬紝鎺ヤ笅鏉ユ垜鍊熷姪鏁板﹂泦鍚堢殑鍒椾婦娉曞拰榪愮畻榪涜屽垎鏋愩

鍏堥氳繃闆嗗悎鐨勫垪涓炬硶鎻忚堪涓や釜琛ㄧ殑鍚勭粍鏁版嵁錛



鍥3.

榪欓噷鍙鏈変袱縐嶆儏鍐碉紝鐩哥瓑鍜屼笉鐩哥瓑銆傚逛簬涓嶇浉絳夛紝鍙鍐嶅垎涓洪儴鍒嗙浉絳夈佸寘鍚銆佸拰瀹屽叏涓嶇浉絳夈備嬌鐢ㄩ泦鍚堟弿榪幫紝鍙浣跨敤浜ら泦錛岄欐腐鉶氭嫙涓繪満錛屽瓙闆嗭紝騫墮泦銆傚備笅闈㈠浘4.錛岄欐腐鉶氭嫙涓繪満錛屾垜鍒椾婦鍑鴻繖鍑犵嶅父瑙佺殑鎯呭喌錛



鍥4.

瀹炵幇

鍦ㄦ暟鎹搴撲腑錛岃佹壘鍑鴻〃table_left鍜岃〃table_right瀛樺湪鐩稿悓鏁版嵁鐨勭粍錛屾柟娉曞緢澶氾紝榪欓噷鎴戝垪鍑轟袱縐嶅父鐢ㄧ殑鏂規硶銆

(涓嬮潰鐨凷QL鑴氭湰錛屾槸浠ュ浘4.鐨勬暟鎹涓哄熀紜鍙傝)

鏂規硶1錛

閫氳繃"Select … From …Order by … xml for path('') "鎶婂悇緇勭殑data鍒楁暟鎹榪炰覆璧鋒潵錛堝傦紝鍥4.鎶妕able_left鐨勭粍#11鐨勫垪data榪炰覆璧鋒潵鎴"data1-data2-data3"錛夛紝鍏朵粬鍒嗙粍錛堝寘鍚琛╰able_right錛変互姝ゆ柟娉曞疄鐜癲ata鍒楁暟鎹榪炰覆璧鋒潵錛涚劧鍚庨氳繃姣旇緝涓よ〃鐨勮繛涓插悗瀛楁墊槸鍚﹀瓨鍦ㄧ浉絳夛紝鑻ユ槸鐩哥瓑灝辮存槑榪欐瘮杈冨氫袱緇勬暟鎹鐩哥瓑錛岀敱姝ゅ彲浠ュ垽鏂鍑鴻〃table_left鐨勫摢緇勬暟鎹鍦ㄨ〃table_right瀛樺湪涓庡畠鏁版嵁瀹屽叏鐩哥瓑鐨勭粍銆

閽堝規柟娉1錛岀編鍥界┖闂達紝闇瑕佸瑰師琛ㄥ炲姞涓涓瀛楁礵ataPath,鐢ㄤ簬瀛樺偍data鍒楁暟鎹榪炰覆鐨勭粨鏋滐紝濡傦細

200)

200)

⑵ sql姣斿逛袱涓琛ㄤ腑鐨勫樊寮傛暟鎹姣旇緝鐨剆ql璇鍙

select
base.name,base.year
,a.鎴愮嘩as[a琛ㄦ垚緇]
,b.鎴愮嘩as[b琛ㄦ垚緇]
,case
whena.鎴愮嘩isnullthen'a琛ㄤ腑涓嶅瓨鍦'
whenb.鎴愮嘩isnullthen'b琛ㄤ腑涓嶅瓨鍦'
whena.鎴愮嘩=b.鎴愮嘩then'鎴愬姛'
else'宸寮'endas姣旇緝緇撴灉
from(
selectname,yearfromtb_a
union
selectname,yearfromtb_b
)asbase
leftjointb_aasaona.name=base.nameanda.year=base.year
leftjointb_basbonb.name=base.nameandb.year=base.year