56,679
社区成员
发帖
与我相关
我的任务
分享
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 ;
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;
SELECT U.Pusn ,
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优化下,就可以拉。SELECT (SELECT DFid,DFItem,Val FROM `DatawlValministry` ,UserMain u WHERE DatawlValministry.DFid=148 AND DatawlValministry.UID=u.UserID),
(SELECT DFid,DFItem,val FROM `DatawlValministry`,UserMain u WHERE DatawlValministry.`UID` =u.UserID AND DFid=149) FROM …………………………
select *,
(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
select *,
(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
userMain表没有u.DTime字段。select *,
(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 U.Pusn ,
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
这是要优化的代码。