测试数据CREATE TABLE mt(
c1 INT PRIMARY KEY,
c3 VARCHAR(30)); CREATE TABLE ct(
e0 int PRIMARY KEY,
e1 int REFERENCES mt(c1),
e2 VARCHAR(30),
e3 int);INSERT INTO mt VALUES(1, 'Jon:');
INSERT INTO mt VALUES(2, 'Andy:');
INSERT INTO ct VALUES(1, 1, '你 ', 1);
INSERT INTO ct VALUES(2, 1, '好 ', 2);
INSERT INTO ct VALUES(3, 2, '吃 ', 1);
INSERT INTO ct VALUES(4, 2, '了 ', 2);
INSERT INTO ct VALUES(5, 2, '吗 ', 3);
简单说明一下:
mt为主表ct为子表,
期望输出
Jon:你好
Andy:吃了么
希望写成一个查询语句
c1 INT PRIMARY KEY,
c3 VARCHAR(30)); CREATE TABLE ct(
e0 int PRIMARY KEY,
e1 int REFERENCES mt(c1),
e2 VARCHAR(30),
e3 int);INSERT INTO mt VALUES(1, 'Jon:');
INSERT INTO mt VALUES(2, 'Andy:');
INSERT INTO ct VALUES(1, 1, '你 ', 1);
INSERT INTO ct VALUES(2, 1, '好 ', 2);
INSERT INTO ct VALUES(3, 2, '吃 ', 1);
INSERT INTO ct VALUES(4, 2, '了 ', 2);
INSERT INTO ct VALUES(5, 2, '吗 ', 3);
简单说明一下:
mt为主表ct为子表,
期望输出
Jon:你好
Andy:吃了么
希望写成一个查询语句
CREATE TABLE mt(
c1 INT PRIMARY KEY,
c3 VARCHAR(30)); CREATE TABLE ct(
e0 int PRIMARY KEY,
e1 int REFERENCES mt(c1),
e2 VARCHAR(30),
e3 int);INSERT INTO mt VALUES(1, 'Jon:');
INSERT INTO mt VALUES(2, 'Andy:');
INSERT INTO ct VALUES(1, 1, '你 ', 1);
INSERT INTO ct VALUES(2, 1, '好 ', 2);
INSERT INTO ct VALUES(3, 2, '吃 ', 1);
INSERT INTO ct VALUES(4, 2, '了 ', 2);
INSERT INTO ct VALUES(5, 2, '吗 ', 3);
goselect a.c3,replace((select ' ' + e2 from ct where e1 = a.c1 for xml path('')),' ','')c1
from mt adrop table ct,mt
c3 c1
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Jon: 你好
Andy: 吃了吗(2 行受影响)
CREATE TABLE mt(
c1 INT PRIMARY KEY,
c3 VARCHAR(30)); CREATE TABLE ct(
e0 int PRIMARY KEY,
e1 int REFERENCES mt(c1),
e2 VARCHAR(30),
e3 int);INSERT INTO mt VALUES(1, 'Jon:');
INSERT INTO mt VALUES(2, 'Andy:');
INSERT INTO ct VALUES(1, 1, '你 ', 1);
INSERT INTO ct VALUES(2, 1, '好 ', 2);
INSERT INTO ct VALUES(3, 2, '吃 ', 1);
INSERT INTO ct VALUES(4, 2, '了 ', 2);
INSERT INTO ct VALUES(5, 2, '吗 ', 3);select c1,c3,str1=replace((select ','+e2 from ct where e1=a.c1 order by e3 for xml path('')),',','')
from mt a group by c1,c3/*
c1 c3 str1
----------- ------------------------------ ---------
1 Jon: 你 好
2 Andy: 吃 了 吗
c1 INT PRIMARY KEY,
c3 VARCHAR(30)); CREATE TABLE ct(
e0 int PRIMARY KEY,
e1 int REFERENCES mt(c1),
e2 VARCHAR(30),
e3 int);INSERT INTO mt VALUES(1, 'Jon:');
INSERT INTO mt VALUES(2, 'Andy:');
INSERT INTO ct VALUES(1, 1, '你 ', 1);
INSERT INTO ct VALUES(2, 1, '好 ', 2);
INSERT INTO ct VALUES(3, 2, '吃 ', 1);
INSERT INTO ct VALUES(4, 2, '了 ', 2);
INSERT INTO ct VALUES(5, 2, '吗 ', 3);
goselect a.c3 + replace((select '' + e2 from ct where e1 = a.c1 for xml path('')),' ','')c1
from mt adrop table ct,mt