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