aa 张三 132000000
aa 李四 133000000
bb 王五 122000000
bb 照六 144000000
bb 无七 155000000
结果：
code name1 phone1 name2 phone2 name3 phone 3 ...
aa 张三 132000000 李四 133000000 ...
bb 王五 122000000 照六 144000000 无七 155000000 ...
有多少条记录就在后面接上多少条！
怎么写存储过程，在线等~~,急··
17 个解决方案
#1
如果你后台的 name1 phone1 ... 是一个字段的内容，用 group_concat（） 就可以了，要写成存储过程你就自己加到 begin end 里吧：
要是多个字段的话，这个还真不知道怎么弄。
select code, group_concat(name, ' ', phone separator ' ')
from 表
grop by code
要是多个字段的话，这个还真不知道怎么弄。
#2
继续等待~~~·
#3
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (code varchar(2),name varchar(4),phone int)
insert into #tb
select 'aa','张三',132000000 union all
select 'aa','李四',133000000 union all
select 'bb','王五',122000000 union all
select 'bb','照六',144000000 union all
select 'bb','无七',155000000
alter table #tb add id int identity(1,1)
select *,n=(select count(*) from #tb where code=t.code and id<=t.id)
into #t
from #tb t
select code,
name1=max(case when n=1 then name end),
phone1=max(case when n=1 then phone end),
name2=max(case when n=2 then name end),
phone2=max(case when n=2 then phone end)
from #t
group by code
code name1 phone1 name2 phone2
---- ----- ----------- ----- -----------
aa 张三 132000000 李四 133000000
bb 王五 122000000 照六 144000000
我查到这个答案，可以只能固定显示2个字段，我想不固定字段，可以有多少就显示多少~~
go
create table #tb (code varchar(2),name varchar(4),phone int)
insert into #tb
select 'aa','张三',132000000 union all
select 'aa','李四',133000000 union all
select 'bb','王五',122000000 union all
select 'bb','照六',144000000 union all
select 'bb','无七',155000000
alter table #tb add id int identity(1,1)
select *,n=(select count(*) from #tb where code=t.code and id<=t.id)
into #t
from #tb t
select code,
name1=max(case when n=1 then name end),
phone1=max(case when n=1 then phone end),
name2=max(case when n=2 then name end),
phone2=max(case when n=2 then phone end)
from #t
group by code
code name1 phone1 name2 phone2
---- ----- ----------- ----- -----------
aa 张三 132000000 李四 133000000
bb 王五 122000000 照六 144000000
我查到这个答案，可以只能固定显示2个字段，我想不固定字段，可以有多少就显示多少~~
#5
DELIMITER $$
DROP PROCEDURE IF EXISTS dtcoltorow$$
CREATE PROCEDURE dtcoltorow()
BEGIN
DECLARE i INT DEFAULT 1;
SET @asql='';
SELECT MAX(bz) INTO @bz FROM (
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a;
SET @asql1='(
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a1';
WHILE i<=@bz DO
SET @asql=CONCAT(@asql,'max(if(bz=',i,',name,"")) as name',i,',','max(if(bz=',i,',phone,"")) as phone',i,',');
SET i=i+1;
END WHILE;
SET @asql=CONCAT('select `code` ,',LEFT(@asql,LENGTH(@asql)-1),' from ',@asql1,' group by `code` ');
SELECT @asql;
PREPARE stml FROM @asql;
EXECUTE stml;
END$$
DELIMITER ;
CALL dtcoltorow;
DROP PROCEDURE IF EXISTS dtcoltorow$$
CREATE PROCEDURE dtcoltorow()
BEGIN
DECLARE i INT DEFAULT 1;
SET @asql='';
SELECT MAX(bz) INTO @bz FROM (
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a;
SET @asql1='(
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a1';
WHILE i<=@bz DO
SET @asql=CONCAT(@asql,'max(if(bz=',i,',name,"")) as name',i,',','max(if(bz=',i,',phone,"")) as phone',i,',');
SET i=i+1;
END WHILE;
SET @asql=CONCAT('select `code` ,',LEFT(@asql,LENGTH(@asql)-1),' from ',@asql1,' group by `code` ');
SELECT @asql;
PREPARE stml FROM @asql;
EXECUTE stml;
END$$
DELIMITER ;
CALL dtcoltorow;
#6
DELIMITER $$
DROP PROCEDURE IF EXISTS dtcoltorow$$
CREATE PROCEDURE dtcoltorow()
BEGIN
DECLARE i INT DEFAULT 1;
SET @asql='';
SELECT MAX(bz) INTO @bz FROM (
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a;
SET @asql1='(
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a1';
WHILE i<=@bz DO
SET @asql=CONCAT(@asql,'max(if(bz=',i,',name,"")) as name',i,',','max(if(bz=',i,',phone,"")) as phone',i,',');
SET i=i+1;
END WHILE;
SET @asql=CONCAT('select `code` ,',LEFT(@asql,LENGTH(@asql)-1),' from ',@asql1,' group by `code` ');
SELECT @asql;
PREPARE stml FROM @asql;
EXECUTE stml;
END$$
DELIMITER ;
CALL dtcoltorow;
DROP PROCEDURE IF EXISTS dtcoltorow$$
CREATE PROCEDURE dtcoltorow()
BEGIN
DECLARE i INT DEFAULT 1;
SET @asql='';
SELECT MAX(bz) INTO @bz FROM (
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a;
SET @asql1='(
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a1';
WHILE i<=@bz DO
SET @asql=CONCAT(@asql,'max(if(bz=',i,',name,"")) as name',i,',','max(if(bz=',i,',phone,"")) as phone',i,',');
SET i=i+1;
END WHILE;
SET @asql=CONCAT('select `code` ,',LEFT(@asql,LENGTH(@asql)-1),' from ',@asql1,' group by `code` ');
SELECT @asql;
PREPARE stml FROM @asql;
EXECUTE stml;
END$$
DELIMITER ;
CALL dtcoltorow;
#7
请最好在这个基础上修改，其他的我都改不了，谢谢了
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (code varchar(2),name varchar(4),phone int)
insert into #tb
select 'aa','张三',132000000 union all
select 'aa','李四',133000000 union all
select 'bb','王五',122000000 union all
select 'bb','照六',144000000 union all
select 'bb','无七',155000000
alter table #tb add id int identity(1,1)
select *,n=(select count(*) from #tb where code=t.code and id<=t.id)
into #t
from #tb t
select code,
name1=max(case when n=1 then name end),
phone1=max(case when n=1 then phone end),
name2=max(case when n=2 then name end),
phone2=max(case when n=2 then phone end)
from #t
group by code
code name1 phone1 name2 phone2
---- ----- ----------- ----- -----------
aa 张三 132000000 李四 133000000
bb 王五 122000000 照六 144000000
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (code varchar(2),name varchar(4),phone int)
insert into #tb
select 'aa','张三',132000000 union all
select 'aa','李四',133000000 union all
select 'bb','王五',122000000 union all
select 'bb','照六',144000000 union all
select 'bb','无七',155000000
alter table #tb add id int identity(1,1)
select *,n=(select count(*) from #tb where code=t.code and id<=t.id)
into #t
from #tb t
select code,
name1=max(case when n=1 then name end),
phone1=max(case when n=1 then phone end),
name2=max(case when n=2 then name end),
phone2=max(case when n=2 then phone end)
from #t
group by code
code name1 phone1 name2 phone2
---- ----- ----------- ----- -----------
aa 张三 132000000 李四 133000000
bb 王五 122000000 照六 144000000
#8
wwwa,您能不能把字段和表名开始标识好，我看不懂，比如bz是什么？
#9
sorry 是wwwwa
#10
假设你使用MYSQL，加入自增字段ID
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`
看看结果
自己动手做一下吧
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`
看看结果
自己动手做一下吧
#11
wwwwa,大哥，我那个这样写，
begin
declare i int default 1;
set @asql='';
select max(rowcount) into @rowcount from ( select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType) a;
set @asql1='(
select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType
) a1';
while i<=@rowcount do
set @aspl=concat(@asql,'max(if(rowcount=',i,',sowId,"")) as sowId',i,',','max(if(rowcount=',i,',matchingDate,"")) as matchingDate',i,',','max(if(rowcount=',i,',matchingType,"")) as matchingType',i,',' );
set i=i+1;
end while;
set @asql=concat('select boarId',left (@asql,length(@asql)-1),' from ',@asql1,' group by boarId ' );
select @asql;
prepare matching from @asql;
execute matching;
end
可是，运行后只是一个字段@asql,值就是代码，再运行里面的代码值，只得到一列！
begin
declare i int default 1;
set @asql='';
select max(rowcount) into @rowcount from ( select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType) a;
set @asql1='(
select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType
) a1';
while i<=@rowcount do
set @aspl=concat(@asql,'max(if(rowcount=',i,',sowId,"")) as sowId',i,',','max(if(rowcount=',i,',matchingDate,"")) as matchingDate',i,',','max(if(rowcount=',i,',matchingType,"")) as matchingType',i,',' );
set i=i+1;
end while;
set @asql=concat('select boarId',left (@asql,length(@asql)-1),' from ',@asql1,' group by boarId ' );
select @asql;
prepare matching from @asql;
execute matching;
end
可是，运行后只是一个字段@asql,值就是代码，再运行里面的代码值，只得到一列！
#12
@asql内容是什么，在MYSQL中运行，结果是什么
#13
select boarId from (
select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType
) a1 group by boarId
这是@asql的内容，运行的结果
+--------+
| boarId |
+--------+
| 312 |
| 321 |
| 326 |
| 330 |
| 334 |
| 335 |
+--------+
6 rows in set
这是原表，在这个表基础上查
+--------+-------+--------------+---------------------+
| boarid | sowid | matchingtype | matchingdate |
+--------+-------+--------------+---------------------+
| 312 | 311 | 2 | 2011-08-25 00:00:00 |
| 321 | 308 | 1 | 2011-08-05 00:00:00 |
| 326 | 311 | 1 | 2011-06-06 00:00:00 |
| 330 | 308 | 1 | 2011-08-03 00:00:00 |
| 330 | 308 | 1 | 2011-05-05 00:00:00 |
| 330 | 311 | 1 | 2011-06-14 00:00:00 |
| 334 | 311 | 1 | 2011-08-11 00:00:00 |
| 335 | 308 | 1 | 2011-08-17 00:00:00 |
| 330 | 308 | 1 | 2011-09-01 00:00:00 |
+--------+-------+--------------+---------------------+
9 rows in set
select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType
) a1 group by boarId
这是@asql的内容，运行的结果
+--------+
| boarId |
+--------+
| 312 |
| 321 |
| 326 |
| 330 |
| 334 |
| 335 |
+--------+
6 rows in set
这是原表，在这个表基础上查
+--------+-------+--------------+---------------------+
| boarid | sowid | matchingtype | matchingdate |
+--------+-------+--------------+---------------------+
| 312 | 311 | 2 | 2011-08-25 00:00:00 |
| 321 | 308 | 1 | 2011-08-05 00:00:00 |
| 326 | 311 | 1 | 2011-06-06 00:00:00 |
| 330 | 308 | 1 | 2011-08-03 00:00:00 |
| 330 | 308 | 1 | 2011-05-05 00:00:00 |
| 330 | 311 | 1 | 2011-06-14 00:00:00 |
| 334 | 311 | 1 | 2011-08-11 00:00:00 |
| 335 | 308 | 1 | 2011-08-17 00:00:00 |
| 330 | 308 | 1 | 2011-09-01 00:00:00 |
+--------+-------+--------------+---------------------+
9 rows in set
#14
set
@asql=concat(@asql,'max(if(rowcount=',i,',sowId,"")) as sowId',i,',','max(if(rowcount=',i,',matchingDate,"")) as matchingDate',i,',','max(if(rowcount=',i,',matchingType,"")) as matchingType',i,',' );
#15
我是这样写的，现在有两个问题，第一那个循环好像没起作用，还有就是最后运行还是一个字段值为语句！
begin
declare i int default 1;
set @asql='';
select max(rowcount) into @rowcount from ( select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType) a;
set @asql1='(
select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType
) a1';
while i<=@rowcount do
set @aspl=concat(@asql,'max(if(rowcount=',i,',sowId,"")) as sowId',i,',','max(if(rowcount=',i,',matchingDate,"")) as matchingDate',i,',','max(if(rowcount=',i,',matchingType,"")) as matchingType',i,',' );
set i=i+1;
end while;
set @asql=concat('select boarId,(select `group` from piginfor where id=boarId) as `group` ',left (@asql,length(@asql )-1),' from ',@asql1,' group by boarId ' );
select @asql;
prepare matching from @asql;
execute matching;
end
begin
declare i int default 1;
set @asql='';
select max(rowcount) into @rowcount from ( select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType) a;
set @asql1='(
select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType
) a1';
while i<=@rowcount do
set @aspl=concat(@asql,'max(if(rowcount=',i,',sowId,"")) as sowId',i,',','max(if(rowcount=',i,',matchingDate,"")) as matchingDate',i,',','max(if(rowcount=',i,',matchingType,"")) as matchingType',i,',' );
set i=i+1;
end while;
set @asql=concat('select boarId,(select `group` from piginfor where id=boarId) as `group` ',left (@asql,length(@asql )-1),' from ',@asql1,' group by boarId ' );
select @asql;
prepare matching from @asql;
execute matching;
end
#16
1、根据我的SP自行修改；
2、看看14楼的 提示，你修改没有？
2、看看14楼的 提示，你修改没有？
#17
非常感谢你，终于可以了！我把那个select @asql 去掉就好!
#1
如果你后台的 name1 phone1 ... 是一个字段的内容，用 group_concat（） 就可以了，要写成存储过程你就自己加到 begin end 里吧：
要是多个字段的话，这个还真不知道怎么弄。
select code, group_concat(name, ' ', phone separator ' ')
from 表
grop by code
要是多个字段的话，这个还真不知道怎么弄。
#2
继续等待~~~·
#3
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (code varchar(2),name varchar(4),phone int)
insert into #tb
select 'aa','张三',132000000 union all
select 'aa','李四',133000000 union all
select 'bb','王五',122000000 union all
select 'bb','照六',144000000 union all
select 'bb','无七',155000000
alter table #tb add id int identity(1,1)
select *,n=(select count(*) from #tb where code=t.code and id<=t.id)
into #t
from #tb t
select code,
name1=max(case when n=1 then name end),
phone1=max(case when n=1 then phone end),
name2=max(case when n=2 then name end),
phone2=max(case when n=2 then phone end)
from #t
group by code
code name1 phone1 name2 phone2
---- ----- ----------- ----- -----------
aa 张三 132000000 李四 133000000
bb 王五 122000000 照六 144000000
我查到这个答案，可以只能固定显示2个字段，我想不固定字段，可以有多少就显示多少~~
go
create table #tb (code varchar(2),name varchar(4),phone int)
insert into #tb
select 'aa','张三',132000000 union all
select 'aa','李四',133000000 union all
select 'bb','王五',122000000 union all
select 'bb','照六',144000000 union all
select 'bb','无七',155000000
alter table #tb add id int identity(1,1)
select *,n=(select count(*) from #tb where code=t.code and id<=t.id)
into #t
from #tb t
select code,
name1=max(case when n=1 then name end),
phone1=max(case when n=1 then phone end),
name2=max(case when n=2 then name end),
phone2=max(case when n=2 then phone end)
from #t
group by code
code name1 phone1 name2 phone2
---- ----- ----------- ----- -----------
aa 张三 132000000 李四 133000000
bb 王五 122000000 照六 144000000
我查到这个答案，可以只能固定显示2个字段，我想不固定字段，可以有多少就显示多少~~
#4
#5
DELIMITER $$
DROP PROCEDURE IF EXISTS dtcoltorow$$
CREATE PROCEDURE dtcoltorow()
BEGIN
DECLARE i INT DEFAULT 1;
SET @asql='';
SELECT MAX(bz) INTO @bz FROM (
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a;
SET @asql1='(
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a1';
WHILE i<=@bz DO
SET @asql=CONCAT(@asql,'max(if(bz=',i,',name,"")) as name',i,',','max(if(bz=',i,',phone,"")) as phone',i,',');
SET i=i+1;
END WHILE;
SET @asql=CONCAT('select `code` ,',LEFT(@asql,LENGTH(@asql)-1),' from ',@asql1,' group by `code` ');
SELECT @asql;
PREPARE stml FROM @asql;
EXECUTE stml;
END$$
DELIMITER ;
CALL dtcoltorow;
DROP PROCEDURE IF EXISTS dtcoltorow$$
CREATE PROCEDURE dtcoltorow()
BEGIN
DECLARE i INT DEFAULT 1;
SET @asql='';
SELECT MAX(bz) INTO @bz FROM (
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a;
SET @asql1='(
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a1';
WHILE i<=@bz DO
SET @asql=CONCAT(@asql,'max(if(bz=',i,',name,"")) as name',i,',','max(if(bz=',i,',phone,"")) as phone',i,',');
SET i=i+1;
END WHILE;
SET @asql=CONCAT('select `code` ,',LEFT(@asql,LENGTH(@asql)-1),' from ',@asql1,' group by `code` ');
SELECT @asql;
PREPARE stml FROM @asql;
EXECUTE stml;
END$$
DELIMITER ;
CALL dtcoltorow;
#6
DELIMITER $$
DROP PROCEDURE IF EXISTS dtcoltorow$$
CREATE PROCEDURE dtcoltorow()
BEGIN
DECLARE i INT DEFAULT 1;
SET @asql='';
SELECT MAX(bz) INTO @bz FROM (
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a;
SET @asql1='(
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a1';
WHILE i<=@bz DO
SET @asql=CONCAT(@asql,'max(if(bz=',i,',name,"")) as name',i,',','max(if(bz=',i,',phone,"")) as phone',i,',');
SET i=i+1;
END WHILE;
SET @asql=CONCAT('select `code` ,',LEFT(@asql,LENGTH(@asql)-1),' from ',@asql1,' group by `code` ');
SELECT @asql;
PREPARE stml FROM @asql;
EXECUTE stml;
END$$
DELIMITER ;
CALL dtcoltorow;
DROP PROCEDURE IF EXISTS dtcoltorow$$
CREATE PROCEDURE dtcoltorow()
BEGIN
DECLARE i INT DEFAULT 1;
SET @asql='';
SELECT MAX(bz) INTO @bz FROM (
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a;
SET @asql1='(
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`) a1';
WHILE i<=@bz DO
SET @asql=CONCAT(@asql,'max(if(bz=',i,',name,"")) as name',i,',','max(if(bz=',i,',phone,"")) as phone',i,',');
SET i=i+1;
END WHILE;
SET @asql=CONCAT('select `code` ,',LEFT(@asql,LENGTH(@asql)-1),' from ',@asql1,' group by `code` ');
SELECT @asql;
PREPARE stml FROM @asql;
EXECUTE stml;
END$$
DELIMITER ;
CALL dtcoltorow;
#7
请最好在这个基础上修改，其他的我都改不了，谢谢了
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (code varchar(2),name varchar(4),phone int)
insert into #tb
select 'aa','张三',132000000 union all
select 'aa','李四',133000000 union all
select 'bb','王五',122000000 union all
select 'bb','照六',144000000 union all
select 'bb','无七',155000000
alter table #tb add id int identity(1,1)
select *,n=(select count(*) from #tb where code=t.code and id<=t.id)
into #t
from #tb t
select code,
name1=max(case when n=1 then name end),
phone1=max(case when n=1 then phone end),
name2=max(case when n=2 then name end),
phone2=max(case when n=2 then phone end)
from #t
group by code
code name1 phone1 name2 phone2
---- ----- ----------- ----- -----------
aa 张三 132000000 李四 133000000
bb 王五 122000000 照六 144000000
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (code varchar(2),name varchar(4),phone int)
insert into #tb
select 'aa','张三',132000000 union all
select 'aa','李四',133000000 union all
select 'bb','王五',122000000 union all
select 'bb','照六',144000000 union all
select 'bb','无七',155000000
alter table #tb add id int identity(1,1)
select *,n=(select count(*) from #tb where code=t.code and id<=t.id)
into #t
from #tb t
select code,
name1=max(case when n=1 then name end),
phone1=max(case when n=1 then phone end),
name2=max(case when n=2 then name end),
phone2=max(case when n=2 then phone end)
from #t
group by code
code name1 phone1 name2 phone2
---- ----- ----------- ----- -----------
aa 张三 132000000 李四 133000000
bb 王五 122000000 照六 144000000
#8
wwwa,您能不能把字段和表名开始标识好，我看不懂，比如bz是什么？
#9
sorry 是wwwwa
#10
假设你使用MYSQL，加入自增字段ID
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`
看看结果
自己动手做一下吧
SELECT a.`ID`,a.`code`,a.`name`,a.`phone`,COUNT(b.id) AS bz FROM ttl2 a LEFT JOIN ttl2 b
ON a.`code`=b.`code` AND a.`ID`>=b.`ID` GROUP BY a.`ID`,a.`code`,a.`name`,a.`phone`
看看结果
自己动手做一下吧
#11
wwwwa,大哥，我那个这样写，
begin
declare i int default 1;
set @asql='';
select max(rowcount) into @rowcount from ( select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType) a;
set @asql1='(
select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType
) a1';
while i<=@rowcount do
set @aspl=concat(@asql,'max(if(rowcount=',i,',sowId,"")) as sowId',i,',','max(if(rowcount=',i,',matchingDate,"")) as matchingDate',i,',','max(if(rowcount=',i,',matchingType,"")) as matchingType',i,',' );
set i=i+1;
end while;
set @asql=concat('select boarId',left (@asql,length(@asql)-1),' from ',@asql1,' group by boarId ' );
select @asql;
prepare matching from @asql;
execute matching;
end
可是，运行后只是一个字段@asql,值就是代码，再运行里面的代码值，只得到一列！
begin
declare i int default 1;
set @asql='';
select max(rowcount) into @rowcount from ( select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType) a;
set @asql1='(
select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType
) a1';
while i<=@rowcount do
set @aspl=concat(@asql,'max(if(rowcount=',i,',sowId,"")) as sowId',i,',','max(if(rowcount=',i,',matchingDate,"")) as matchingDate',i,',','max(if(rowcount=',i,',matchingType,"")) as matchingType',i,',' );
set i=i+1;
end while;
set @asql=concat('select boarId',left (@asql,length(@asql)-1),' from ',@asql1,' group by boarId ' );
select @asql;
prepare matching from @asql;
execute matching;
end
可是，运行后只是一个字段@asql,值就是代码，再运行里面的代码值，只得到一列！
#12
@asql内容是什么，在MYSQL中运行，结果是什么
#13
select boarId from (
select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType
) a1 group by boarId
这是@asql的内容，运行的结果
+--------+
| boarId |
+--------+
| 312 |
| 321 |
| 326 |
| 330 |
| 334 |
| 335 |
+--------+
6 rows in set
这是原表，在这个表基础上查
+--------+-------+--------------+---------------------+
| boarid | sowid | matchingtype | matchingdate |
+--------+-------+--------------+---------------------+
| 312 | 311 | 2 | 2011-08-25 00:00:00 |
| 321 | 308 | 1 | 2011-08-05 00:00:00 |
| 326 | 311 | 1 | 2011-06-06 00:00:00 |
| 330 | 308 | 1 | 2011-08-03 00:00:00 |
| 330 | 308 | 1 | 2011-05-05 00:00:00 |
| 330 | 311 | 1 | 2011-06-14 00:00:00 |
| 334 | 311 | 1 | 2011-08-11 00:00:00 |
| 335 | 308 | 1 | 2011-08-17 00:00:00 |
| 330 | 308 | 1 | 2011-09-01 00:00:00 |
+--------+-------+--------------+---------------------+
9 rows in set
select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType
) a1 group by boarId
这是@asql的内容，运行的结果
+--------+
| boarId |
+--------+
| 312 |
| 321 |
| 326 |
| 330 |
| 334 |
| 335 |
+--------+
6 rows in set
这是原表，在这个表基础上查
+--------+-------+--------------+---------------------+
| boarid | sowid | matchingtype | matchingdate |
+--------+-------+--------------+---------------------+
| 312 | 311 | 2 | 2011-08-25 00:00:00 |
| 321 | 308 | 1 | 2011-08-05 00:00:00 |
| 326 | 311 | 1 | 2011-06-06 00:00:00 |
| 330 | 308 | 1 | 2011-08-03 00:00:00 |
| 330 | 308 | 1 | 2011-05-05 00:00:00 |
| 330 | 311 | 1 | 2011-06-14 00:00:00 |
| 334 | 311 | 1 | 2011-08-11 00:00:00 |
| 335 | 308 | 1 | 2011-08-17 00:00:00 |
| 330 | 308 | 1 | 2011-09-01 00:00:00 |
+--------+-------+--------------+---------------------+
9 rows in set
#14
set
@asql=concat(@asql,'max(if(rowcount=',i,',sowId,"")) as sowId',i,',','max(if(rowcount=',i,',matchingDate,"")) as matchingDate',i,',','max(if(rowcount=',i,',matchingType,"")) as matchingType',i,',' );
#15
我是这样写的，现在有两个问题，第一那个循环好像没起作用，还有就是最后运行还是一个字段值为语句！
begin
declare i int default 1;
set @asql='';
select max(rowcount) into @rowcount from ( select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType) a;
set @asql1='(
select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType
) a1';
while i<=@rowcount do
set @aspl=concat(@asql,'max(if(rowcount=',i,',sowId,"")) as sowId',i,',','max(if(rowcount=',i,',matchingDate,"")) as matchingDate',i,',','max(if(rowcount=',i,',matchingType,"")) as matchingType',i,',' );
set i=i+1;
end while;
set @asql=concat('select boarId,(select `group` from piginfor where id=boarId) as `group` ',left (@asql,length(@asql )-1),' from ',@asql1,' group by boarId ' );
select @asql;
prepare matching from @asql;
execute matching;
end
begin
declare i int default 1;
set @asql='';
select max(rowcount) into @rowcount from ( select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType) a;
set @asql1='(
select pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType,count(pm2.id) as rowcount from pig_matching pm1 left join pig_matching pm2 on pm1.boarId=pm2.boarId and pm1.id>=pm2.id group by pm1.id,pm1.boarId,pm1.sowId,pm1.matchingDate,pm1.matchingType
) a1';
while i<=@rowcount do
set @aspl=concat(@asql,'max(if(rowcount=',i,',sowId,"")) as sowId',i,',','max(if(rowcount=',i,',matchingDate,"")) as matchingDate',i,',','max(if(rowcount=',i,',matchingType,"")) as matchingType',i,',' );
set i=i+1;
end while;
set @asql=concat('select boarId,(select `group` from piginfor where id=boarId) as `group` ',left (@asql,length(@asql )-1),' from ',@asql1,' group by boarId ' );
select @asql;
prepare matching from @asql;
execute matching;
end
#16
1、根据我的SP自行修改；
2、看看14楼的 提示，你修改没有？
2、看看14楼的 提示，你修改没有？
#17
非常感谢你，终于可以了！我把那个select @asql 去掉就好!