现在有两个表A和B
表A
ID NAME
-------------
1 a
2 b
3 c
4 d
---------------------------
表B
ID NAME COL1 COL2 COL3
-------------------------------------------
1 A A1 A2 A3
两个表之间没有外键关联的关系
我现在想要得到的结果是
ID NAME COL1 COL2 COL3
-------------------------------------------
1-1 A-a A1 A2 A3
1-2 A-b A1 A2 A3
1-3 A-c A1 A2 A3
1-4 A-d A1 A2 A3
请问这个SQL语句怎么写
------解决方案--------------------
- SQL code
;WITH B(id,name,col1,col2,col3) AS( select 1, 'A', 'A1', 'A2', 'A3'),A(id, name) as( select 1,'a' union all select 2,'b' union all select 3,'c' union all select 4,'d')select CAST(b.id AS VARCHAR(10)) + '-' + CAST(a.id AS VARCHAR(10)) id, b.name + '-' + a.name name, b.col1, b.col2, b.col3from B bcross join A a/*id name col1 col2 col3--------------------- ---- ---- ---- ----1-1 A-a A1 A2 A31-2 A-b A1 A2 A31-3 A-c A1 A2 A31-4 A-d A1 A2 A3(4 row(s) affected)*/