如何用sql语句查询两张表中的相同字段数据 如何用SQL语句查询两张表中的相同字段数据
sql\u8bed\u53e5\u5982\u4f55\u67e5\u8be2\u4e00\u4e2a\u8868\u4e2d\u67d0\u4e24\u4e2a\u5b57\u6bb5\u7684\u76f8\u540c\u6570\u636e?\u67e5\u8be2\u4e00\u4e2a\u8868\u4e2d\u67d0\u4e24\u4e2a\u5b57\u6bb5\u7684\u76f8\u540c\u6570\u636e\u4ee3\u7801\u662f\uff1aSelect Name,ID From A group by Name,ID having count (*)>1\u3002
\u7ed3\u6784\u5316\u67e5\u8be2\u8bed\u8a00(Structured Query Language)\u7b80\u79f0SQL\uff0c\u7ed3\u6784\u5316\u67e5\u8be2\u8bed\u8a00\u662f\u4e00\u79cd\u6570\u636e\u5e93\u67e5\u8be2\u548c\u7a0b\u5e8f\u8bbe\u8ba1\u8bed\u8a00\uff0c\u7528\u4e8e\u5b58\u53d6\u6570\u636e\u4ee5\u53ca\u67e5\u8be2\u3001\u66f4\u65b0\u548c\u7ba1\u7406\u5173\u7cfb\u6570\u636e\u5e93\u7cfb\u7edf;
sql \u8bed\u53e5\u5c31\u662f\u5bf9\u6570\u636e\u5e93\u8fdb\u884c\u64cd\u4f5c\u7684\u4e00\u79cd\u8bed\u8a00\u3002
sql="select * from \u6570\u636e\u8868 where\u5b57\u6bb5\u540d=\u5b57\u6bb5\u503c order by\u5b57\u6bb5\u540d[desc]"\uff08\u6309\u67d0\u4e2a\u5b57\u6bb5\u503c\u964d\u5e8f\u6392\u5217\uff0c\u9ed8\u8ba4\u5347\u5e8fASC\uff09\uff1b
sql="select * from \u6570\u636e\u8868 where\u5b57\u6bb5\u540dlike '%\u5b57\u6bb5\u503c%' order by \u5b57\u6bb5\u540d [desc]"\uff1b
sql="select top 10 * from \u6570\u636e\u8868 where\u5b57\u6bb5\u540d=\u5b57\u6bb5\u503c order by \u5b57\u6bb5\u540d [desc]"\uff1b
sql="select top 10 * from \u6570\u636e\u8868 order by \u5b57\u6bb5\u540d [desc]"\uff1b
sql="select * from \u6570\u636e\u8868 where\u5b57\u6bb5\u540din ('\u503c1','\u503c2','\u503c3')"\uff1b
sql="select * from \u6570\u636e\u8868 where\u5b57\u6bb5\u540dbetween \u503c1 and \u503c2"\u3002
sql\u8bed\u53e5\uff1a
\u66f4\u65b0:update table1 set field1=value1 where \u8303\u56f4\uff1b
\u67e5\u627e:select * from table1 where field1 like '%value1%' (\u6240\u6709\u5305\u542b'value1'\u8fd9\u4e2a\u6a21\u5f0f\u7684\u5b57\u7b26\u4e32)\uff1b
\u6392\u5e8f:select * from table1 order by field1,field2 [desc]\uff1b
\u6c42\u548c:select sum(field1) as sumvalue from table1\uff1b
\u5e73\u5747:select avg(field1) as avgvalue from table1\uff1b
\u6700\u5927:select max(field1) as maxvalue from table1\uff1b
\u6700\u5c0f:select min(field1) as minvalue from table1[separator]\u3002
\u9996\u5148\u4f60\u4e24\u5f20\u8868\u6709\u4ec0\u4e48\u5173\u8054\u4e48\uff1f
\u5982\u679c\u6ca1\u6709\u5173\u8054\u5e76\u4e14\u4f60\u53ea\u662f\u60f3\u8981\u76f8\u540c\u5b57\u6bb5\u7684\u6570\u636e\u7684\u8bdd\uff0c\u4e0b\u9762\u7684sql\u53ef\u4ee5\u6ee1\u8db3\uff1a
a).
--\u81ea\u52a8\u53bb\u6389A\uff0cB\u8868\u4e2d\u76f8\u540c\u7684\u6570\u636e
select same_col from Aunionselect same_col from B
b).
--\u53bbA\uff0cB\u8868\u7684\u6700\u5927\u5e76\u96c6
select same_col from Aunion allselect same_col from B
除了我们在上面的例子中使用的 INNER JOIN（内连接），我们还可以使用其他几种连接。
下面列出了您可以使用的 JOIN 类型，以及它们之间的差异。
JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行
select * from 表1 t1 inner join 表2 t2 on t1.字段名=t2.字段名
select 表1.*,表2.* from 表1 inner join 表2 on 表1.id = 表2.表1_id
这样就查出表1中关联表2的信息了
select * from A inner join b on a.col=b.col
绛旓細1銆乼able1 涓 TArea 涓 CC 鐨勬暟鎹 select * from table1 where TArea = 'CC';2銆乀abel2 涓 Aname 涓 ff 鐨勬暟鎹 select * from Tabel2 where Aname = 'ff';濡傛灉浣犵殑鐩殑鏄彧瑕佺涓夋缁撴灉鐨勮瘽锛岀洿鎺ヤ娇鐢ㄤ笅闈㈣繖涓:3銆佹渶鍚庢壘鍑 1,2 涓 No 鐩稿悓鐨勬暟鎹,寰楀埌杩欎簺鏁版嵁閲屾墍鏈夌殑FFF select a...
绛旓細鍋囪琛1涓簍able1 锛琛2涓簍able2 select a.col from (select column_name col from user_tab_columns where table_name = 'table1') a ,(select column_name col from user_tab_columns where table_name = 'table2') b where a.col = b.col 杩欐牱灏卞彲浠鏌ヨ鍑涓や釜琛寰楃浉鍚屽瓧娈典簡 ...
绛旓細浣跨敤except鍑芥暟锛宻elect * from B where (select count(1) from A where A.ID = B.ID) = 0.鏂规硶涓锛堟帹鑽愶級WITH C AS ( SELECT Name FROM ConsumerCouponApply A WHERE NOT EXISTS ( SELECT 1 FROM ConsumerCouponApply B WHERE B.Name = A.Name GROUP BY B.Name )GROUP...
绛旓細濡備綍鐢╯ql璇彞鏌ヨ涓や釜琛ㄤ腑瀹屽叏鐩稿悓鐨勪袱鏉¤褰 select f1,f2,...,fn from table group by f1,f2,...,fn having count(1)>1 鏌ュ嚭瀛樺湪鐩稿悓鐨刦1,f2,..,fn 鎯虫煡鎵惧嚭璁板綍鍒 select t1.* from table t1 join (select f1,f2,...,fn from table group by f1,f2,...,fn having count(1)...
绛旓細鏌ヨ涓ゅ紶琛ㄤ腑鐨鏁版嵁鍙互閲囧彇杩炴帴鍜岃仈鍚堢殑鏂规硶鏉ュ悎骞躲佺粍鍚堟潵鑷笉鍚岃〃閲岀殑鏁版嵁 锛屽叾涓繛鎺ュ張鍙互鏈夊唴杩炴帴銆佸杩炴帴鍜岃嚜鐒惰繛鎺ョ瓑澶氱褰㈠紡锛岃繛鎺ユ潯浠跺彲浠ユ牴鎹渶瑕佷换鏄撹缃紝浜﹀彲浠ュ绛夎繛鎺ヤ篃鍙互闈炲绛夎繛鎺ワ紝杩樺彲浠ョ粍鍚堜娇鐢ㄩ昏緫杩愮畻绗﹁缃繛鎺ユ潯浠躲傚叿浣鐨凷QL璇彞蹇呴』鎹変緵琛ㄧ粨鏋勫拰杈撳嚭瑕佹眰鎵嶈兘缁欏嚭锛岃鍙ュ舰寮忕殑...
绛旓細union all select * from B where not exists (select 1 from A where A.id=B.id);璇存槑锛氱涓涓瓙鏌ヨ閫夊嚭A琛ㄤ腑鏈嚭鐜颁簬B琛ㄩ噷鐨璁板綍锛涚浜屼釜瀛愭煡璇㈤夊嚭B琛ㄤ腑鏈嚭鐜颁簬A琛ㄩ噷鐨勮褰曘備袱鑰呭悎骞跺嵆涓轰簰涓嶇浉鍚岀殑璁板綍銆缁撴瀯鍖栨煡璇㈣瑷(Structured Query Language)绠绉SQL(鍙戦煶锛/ˈes kju&#...
绛旓細SELECT 瀛︾敓琛.濮撳悕,瀛︾敓琛.鎬у埆,瀛︾敓琛.姘戞棌,鎴愮哗琛.瀛楁2,鎴愮哗琛.瀛楁3 FROM 瀛︾敓琛,鎴愮哗琛 WHERE 瀛︾敓琛.濮撳悕 = 鎴愮哗琛.濮撳悕 AND 瀛︾敓琛.鎬у埆 = '鐢'AND 鎴愮哗琛.瀛楁3 > 36 鎴 SELECT 瀛︾敓琛.*,鎴愮哗琛.FROM 瀛︾敓琛,鎴愮哗琛 WHERE 瀛︾敓琛.濮撳悕 = 鎴愮哗琛.濮撳悕 AND 瀛︾敓琛.鎬у埆 ...
绛旓細select tableA.column1,tableA.column2 from tableA 锛宎bleB where tableA .column1=tableB .column1 鎴栬呬娇鐢 union 鏂规硶锛屾敞鎰涓や釜琛閫夊嚭鏉ョ殑瀛楁鑷冲皯瑕佹牸寮忕浉鍚 select column1,column2,column3 from tableA union select column1,column2,column3 from tableB ...
绛旓細鍋囪涓や釜琛閮芥湁鍞竴閿甎serID 鍙互杩欎箞鍐(浣跨敤鍏ㄨ繛鎺ャ恌ull outer join銆戯細瀹屾暣澶栭儴鑱旀帴杩斿洖宸﹁〃鍜屽彸琛ㄤ腑鐨鎵鏈夎銆傚綋鏌愯鍦ㄥ彟涓涓〃涓娌℃湁鍖归厤琛屾椂锛屽垯鍙︿竴涓〃鐨勯夋嫨鍒楄〃鍒楀寘鍚┖鍊笺傚鏋滆〃涔嬮棿鏈夊尮閰嶈锛屽垯鏁翠釜缁撴灉闆嗚鍖呭惈鍩鸿〃鐨勬暟鎹笺)select *from RCSA_UserInfoDEL a full outer...
绛旓細select * from A inner join b on a.col=b.col 闄や簡鎴戜滑鍦ㄤ笂闈㈢殑渚嬪瓙涓浣跨敤鐨 INNER JOIN锛堝唴杩炴帴锛夛紝鎴戜滑杩樺彲浠ヤ娇鐢ㄥ叾浠栧嚑绉嶈繛鎺ャ備笅闈㈠垪鍑轰簡鎮ㄥ彲浠ヤ娇鐢ㄧ殑 JOIN 绫诲瀷锛屼互鍙婂畠浠箣闂寸殑宸紓銆侸OIN: 濡傛灉琛ㄤ腑鏈夎嚦灏戜竴涓鍖归厤锛屽垯杩斿洖琛 LEFT JOIN: 鍗充娇鍙宠〃涓病鏈夊尮閰嶏紝涔熶粠宸﹁〃杩斿洖鎵鏈夌殑琛 RIGHT...
