有一张users表,很简单的,就保存了用户名和暗码,然后有一个自增字段。CREATE TABLE IF NOT EXISTS `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
`pass` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM ;
上线运行之后,挺好用的。
不过,有一天,客户决意还要保存用户的德律风号码,春秋,性别,住址等信息。之后我就是设置为mysql key/value形式保存数据。
CREATE TABLE IF NOT EXISTS `user_details` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`user_id` INT(11) NOT NULL,
`key` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`value` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;但是现在的问题是我要根据id来显示对应的key和value。不知道sql该如何写?
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
`pass` VARCHAR(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM ;
上线运行之后,挺好用的。
不过,有一天,客户决意还要保存用户的德律风号码,春秋,性别,住址等信息。之后我就是设置为mysql key/value形式保存数据。
CREATE TABLE IF NOT EXISTS `user_details` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`user_id` INT(11) NOT NULL,
`key` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`value` VARCHAR(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;但是现在的问题是我要根据id来显示对应的key和value。不知道sql该如何写?
inner join user_details b on b.user_id=a.id where a.id=1
inner join user_details c on c.user_id=a.id where a.id=1
inner join user_details d on d.user_id=a.id where a.id=1
inner join user_details e on e.user_id=a.id where a.id=1
inner join user_details f on f.user_id=a.id where a.id=1 …………这样的话不是写多个left join 吗?那运行起来比较慢。
select b.key,b.value,c.key,c.val,d.key,d.value,e.key,e,val,f.key,f.value from users a
inner join user_details b on b.user_id=a.id where a.id=1
inner join user_details c on c.user_id=a.id where a.id=2
inner join user_details d on d.user_id=a.id where a.id=3
inner join user_details e on e.user_id=a.id where a.id=4
inner join user_details f on f.user_id=a.id where a.id=4 …………
(select value from `user_details` where `user_id` =a.id and `key` ='性别') as `性别`,
.....
from users a
d1.DfItem,d1.Val,
d2.DfItem,d2.Val,
d3.DfItem,d3.Val,
d4.DfItem,d4.Val,
d5.DfItem,d5.Val,
d6.DfItem,d6.Val,
d7.DfItem,d7.Val,
d8.DfItem,d8.Val,
d9.DfItem,d9.Val,
d10.DfItem,d10.Val,
d11.DfItem,d11.Val,
d12.DfItem,d12.Val,
d13.DfItem,d13.Val,
d14.DfItem,d14.Val,
d15.DfItem,d15.Val,
d16.DfItem,d16.Val,
d17.DfItem,d17.Val,
d18.DfItem,d18.Val,
d19.DfItem,d19.Val,
d20.DfItem,d20.Val,
d21.DfItem,d21.Val,
DatawlValministry.DFid,
DatawlValministry.UID,
DatawlValministry.DTime,
DatawlValministry.FstCreate,DatawlValministry.LastModify FROM UserMain U
LEFT JOIN DatawlValministry ON U.UserID=DatawlValministry.UID
LEFT JOIN DatawlValministry d1 ON U.UserID=d1.UID AND DatawlValministry.DTime=d1.DTime AND d1.Dfid=148
LEFT JOIN DatawlValministry d2 ON d2.UID=U.UserID AND DatawlValministry.DTime=d2.DTime AND d2.Dfid=149
LEFT JOIN DatawlValministry d3 ON d3.UID=U.UserID AND DatawlValministry.DTime=d3.DTime AND d3.Dfid=273
LEFT JOIN DatawlValministry d4 ON d4.UID=U.UserID AND DatawlValministry.DTime=d4.DTime AND d4.Dfid=274
LEFT JOIN DatawlValministry d5 ON d5.UID=U.UserID AND DatawlValministry.DTime=d5.DTime AND d5.Dfid=306
LEFT JOIN DatawlValministry d6 ON d6.UID=U.UserID AND DatawlValministry.DTime=d6.DTime AND d6.Dfid=307
LEFT JOIN DatawlValministry d7 ON d7.UID=U.UserID AND DatawlValministry.DTime=d7.DTime AND d7.Dfid=254
LEFT JOIN DatawlValministry d8 ON d8.UID=U.UserID AND DatawlValministry.DTime=d8.DTime AND d8.Dfid=150
LEFT JOIN DatawlValministry d9 ON d9.UID=U.UserID AND DatawlValministry.DTime=d9.DTime AND d9.Dfid=255
LEFT JOIN DatawlValministry d10 ON d10.UID=U.UserID AND DatawlValministry.DTime=d10.DTime AND d10.Dfid=155
LEFT JOIN DatawlValministry d11 ON d11.UID=U.UserID AND DatawlValministry.DTime=d11.DTime AND d11.Dfid=156
LEFT JOIN DatawlValministry d12 ON d12.UID=U.UserID AND DatawlValministry.DTime=d12.DTime AND d12.Dfid=157
LEFT JOIN DatawlValministry d13 ON d13.UID=U.UserID AND DatawlValministry.DTime=d13.DTime AND d13.Dfid=159
LEFT JOIN DatawlValministry d14 ON d14.UID=U.UserID AND DatawlValministry.DTime=d14.DTime AND d14.Dfid=160
LEFT JOIN DatawlValministry d15 ON d15.UID=U.UserID AND DatawlValministry.DTime=d15.DTime AND d15.Dfid=257
LEFT JOIN DatawlValministry d16 ON d16.UID=U.UserID AND DatawlValministry.DTime=d16.DTime AND d16.Dfid=161
LEFT JOIN DatawlValministry d17 ON d17.UID=U.UserID AND DatawlValministry.DTime=d17.DTime AND d17.Dfid=168
LEFT JOIN DatawlValministry d18 ON d18.UID=U.UserID AND DatawlValministry.DTime=d18.DTime AND d18.Dfid=174
LEFT JOIN DatawlValministry d19 ON d19.UID=U.UserID AND DatawlValministry.DTime=d19.DTime AND d19.Dfid=181
LEFT JOIN DatawlValministry d20 ON d20.UID=U.UserID AND DatawlValministry.DTime=d20.DTime AND d20.Dfid=253
LEFT JOIN DatawlValministry d21 ON d21.UID=U.UserID AND DatawlValministry.DTime=d21.DTime AND d21.Dfid=178
(select value from `DatawlValministry` where `UID` =u.UserID and DTime=u.DTime AND Dfid=148),
(select value from `DatawlValministry` where `UID` =u.UserID and DTime=u.DTime AND Dfid=149),
(select value from `DatawlValministry` where `UID` =u.UserID and DTime=u.DTime AND Dfid=273),
.....
(select value from `DatawlValministry` where `UID` =u.UserID and DTime=u.DTime AND Dfid=178),
from UserMain u
是什么原因呢?
(SELECT DFid,DFItem,val FROM `DatawlValministry`,UserMain u WHERE DatawlValministry.`UID` =u.UserID AND DFid=149) FROM …………………………
贴建表及插入记录的SQL,及要求结果出来看看
d1.DfItem,d1.Val,
d2.DfItem,d2.Val,
d3.DfItem,d3.Val,
d4.DfItem,d4.Val,
d5.DfItem,d5.Val,
d6.DfItem,d6.Val,
d7.DfItem,d7.Val,
d8.DfItem,d8.Val,
d9.DfItem,d9.Val,
d10.DfItem,d10.Val,
d11.DfItem,d11.Val,
d12.DfItem,d12.Val,
d13.DfItem,d13.Val,
d14.DfItem,d14.Val,
d15.DfItem,d15.Val,
d16.DfItem,d16.Val,
d17.DfItem,d17.Val,
d18.DfItem,d18.Val,
d19.DfItem,d19.Val,
d20.DfItem,d20.Val,
d21.DfItem,d21.Val,
DatawlValministry.DFid,
DatawlValministry.UID,
DatawlValministry.DTime,
DatawlValministry.FstCreate,DatawlValministry.LastModify FROM UserMain U
LEFT JOIN DatawlValministry ON U.UserID=DatawlValministry.UID
LEFT JOIN DatawlValministry d1 ON U.UserID=d1.UID AND DatawlValministry.DTime=d1.DTime AND d1.Dfid=148
LEFT JOIN DatawlValministry d2 ON d2.UID=U.UserID AND DatawlValministry.DTime=d2.DTime AND d2.Dfid=149
LEFT JOIN DatawlValministry d3 ON d3.UID=U.UserID AND DatawlValministry.DTime=d3.DTime AND d3.Dfid=273
LEFT JOIN DatawlValministry d4 ON d4.UID=U.UserID AND DatawlValministry.DTime=d4.DTime AND d4.Dfid=274
LEFT JOIN DatawlValministry d5 ON d5.UID=U.UserID AND DatawlValministry.DTime=d5.DTime AND d5.Dfid=306
LEFT JOIN DatawlValministry d6 ON d6.UID=U.UserID AND DatawlValministry.DTime=d6.DTime AND d6.Dfid=307
LEFT JOIN DatawlValministry d7 ON d7.UID=U.UserID AND DatawlValministry.DTime=d7.DTime AND d7.Dfid=254
LEFT JOIN DatawlValministry d8 ON d8.UID=U.UserID AND DatawlValministry.DTime=d8.DTime AND d8.Dfid=150
LEFT JOIN DatawlValministry d9 ON d9.UID=U.UserID AND DatawlValministry.DTime=d9.DTime AND d9.Dfid=255
LEFT JOIN DatawlValministry d10 ON d10.UID=U.UserID AND DatawlValministry.DTime=d10.DTime AND d10.Dfid=155
LEFT JOIN DatawlValministry d11 ON d11.UID=U.UserID AND DatawlValministry.DTime=d11.DTime AND d11.Dfid=156
LEFT JOIN DatawlValministry d12 ON d12.UID=U.UserID AND DatawlValministry.DTime=d12.DTime AND d12.Dfid=157
LEFT JOIN DatawlValministry d13 ON d13.UID=U.UserID AND DatawlValministry.DTime=d13.DTime AND d13.Dfid=159
LEFT JOIN DatawlValministry d14 ON d14.UID=U.UserID AND DatawlValministry.DTime=d14.DTime AND d14.Dfid=160
LEFT JOIN DatawlValministry d15 ON d15.UID=U.UserID AND DatawlValministry.DTime=d15.DTime AND d15.Dfid=257
LEFT JOIN DatawlValministry d16 ON d16.UID=U.UserID AND DatawlValministry.DTime=d16.DTime AND d16.Dfid=161
LEFT JOIN DatawlValministry d17 ON d17.UID=U.UserID AND DatawlValministry.DTime=d17.DTime AND d17.Dfid=168
LEFT JOIN DatawlValministry d18 ON d18.UID=U.UserID AND DatawlValministry.DTime=d18.DTime AND d18.Dfid=174
LEFT JOIN DatawlValministry d19 ON d19.UID=U.UserID AND DatawlValministry.DTime=d19.DTime AND d19.Dfid=181
LEFT JOIN DatawlValministry d20 ON d20.UID=U.UserID AND DatawlValministry.DTime=d20.DTime AND d20.Dfid=253
LEFT JOIN DatawlValministry d21 ON d21.UID=U.UserID AND DatawlValministry.DTime=d21.DTime AND d21.Dfid=178这是要优化的代码。
(select value from `DatawlValministry` where `UID` =u.UserID and DTime=u.DTime AND Dfid=148),
(select value from `DatawlValministry` where `UID` =u.UserID and DTime=u.DTime AND Dfid=149)
from UserMain u楼主原样复制这句执行。
(select value from `DatawlValministry` where `UID` =u.UserID and DTime=u.DTime AND Dfid=148),
(select value from `DatawlValministry` where `UID` =u.UserID and DTime=u.DTime AND Dfid=149)
from UserMain uuserMain表没有u.DTime字段。
(select value from `DatawlValministry` where `UID` =u.UserID AND Dfid=148),
(select value from `DatawlValministry` where `UID` =u.UserID AND Dfid=149)
from UserMain u
d1.DfItem,d1.Val,
d2.DfItem,d2.Val,
d3.DfItem,d3.Val,
d4.DfItem,d4.Val,
d5.DfItem,d5.Val,
d6.DfItem,d6.Val,
d7.DfItem,d7.Val,
d8.DfItem,d8.Val,
d9.DfItem,d9.Val,
d10.DfItem,d10.Val,
d11.DfItem,d11.Val,
d12.DfItem,d12.Val,
d13.DfItem,d13.Val,
d14.DfItem,d14.Val,
d15.DfItem,d15.Val,
d16.DfItem,d16.Val,
d17.DfItem,d17.Val,
d18.DfItem,d18.Val,
d19.DfItem,d19.Val,
d20.DfItem,d20.Val,
d21.DfItem,d21.Val,
DatawlValministry.DFid,
DatawlValministry.UID,
DatawlValministry.DTime,
DatawlValministry.FstCreate,DatawlValministry.LastModify FROM UserMain U
LEFT JOIN DatawlValministry ON U.UserID=DatawlValministry.UID
LEFT JOIN DatawlValministry d1 ON U.UserID=d1.UID AND DatawlValministry.DTime=d1.DTime AND d1.Dfid=148
LEFT JOIN DatawlValministry d2 ON d2.UID=U.UserID AND DatawlValministry.DTime=d2.DTime AND d2.Dfid=149
LEFT JOIN DatawlValministry d3 ON d3.UID=U.UserID AND DatawlValministry.DTime=d3.DTime AND d3.Dfid=273
LEFT JOIN DatawlValministry d4 ON d4.UID=U.UserID AND DatawlValministry.DTime=d4.DTime AND d4.Dfid=274
LEFT JOIN DatawlValministry d5 ON d5.UID=U.UserID AND DatawlValministry.DTime=d5.DTime AND d5.Dfid=306
LEFT JOIN DatawlValministry d6 ON d6.UID=U.UserID AND DatawlValministry.DTime=d6.DTime AND d6.Dfid=307
LEFT JOIN DatawlValministry d7 ON d7.UID=U.UserID AND DatawlValministry.DTime=d7.DTime AND d7.Dfid=254
LEFT JOIN DatawlValministry d8 ON d8.UID=U.UserID AND DatawlValministry.DTime=d8.DTime AND d8.Dfid=150
LEFT JOIN DatawlValministry d9 ON d9.UID=U.UserID AND DatawlValministry.DTime=d9.DTime AND d9.Dfid=255
LEFT JOIN DatawlValministry d10 ON d10.UID=U.UserID AND DatawlValministry.DTime=d10.DTime AND d10.Dfid=155
LEFT JOIN DatawlValministry d11 ON d11.UID=U.UserID AND DatawlValministry.DTime=d11.DTime AND d11.Dfid=156
LEFT JOIN DatawlValministry d12 ON d12.UID=U.UserID AND DatawlValministry.DTime=d12.DTime AND d12.Dfid=157
LEFT JOIN DatawlValministry d13 ON d13.UID=U.UserID AND DatawlValministry.DTime=d13.DTime AND d13.Dfid=159
LEFT JOIN DatawlValministry d14 ON d14.UID=U.UserID AND DatawlValministry.DTime=d14.DTime AND d14.Dfid=160
LEFT JOIN DatawlValministry d15 ON d15.UID=U.UserID AND DatawlValministry.DTime=d15.DTime AND d15.Dfid=257
LEFT JOIN DatawlValministry d16 ON d16.UID=U.UserID AND DatawlValministry.DTime=d16.DTime AND d16.Dfid=161
LEFT JOIN DatawlValministry d17 ON d17.UID=U.UserID AND DatawlValministry.DTime=d17.DTime AND d17.Dfid=168
LEFT JOIN DatawlValministry d18 ON d18.UID=U.UserID AND DatawlValministry.DTime=d18.DTime AND d18.Dfid=174
LEFT JOIN DatawlValministry d19 ON d19.UID=U.UserID AND DatawlValministry.DTime=d19.DTime AND d19.Dfid=181
LEFT JOIN DatawlValministry d20 ON d20.UID=U.UserID AND DatawlValministry.DTime=d20.DTime AND d20.Dfid=253
LEFT JOIN DatawlValministry d21 ON d21.UID=U.UserID AND DatawlValministry.DTime=d21.DTime AND d21.Dfid=178大哥,你把这段sql优化下,就可以拉。
UserMain:UID 建立索引
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
本身你这表结构的设计就有问题。