SET @rownum=0; SELECT *,@rownum:=@rownum+1 as rownum FROM t_person ORDER BY age ASC, height DESC;只能能写到这个程度,抛砖....
嘿嘿 这样呢SET @rownum=0; UPDATE t_person p SET `orderby`= (SELECT rownum FROM (SELECT *,@rownum:=@rownum+1 as rownum FROM t_person ORDER BY age ASC, height DESC) AS r WHERE r.id = p.id) ORDER BY age ASC, height DESC也够麻烦的了
也许不可行, select的时候不允许同时更新.phpmyadmin: You can't specify target table 't_person' for update in FROM clause -- -- 表的结构 `t_person` -- DROP TABLE IF EXISTS `t_person`; CREATE TABLE IF NOT EXISTS `t_person` ( `id` int(11) NOT NULL auto_increment, `age` smallint(3) NOT NULL default '0', `height` smallint(3) NOT NULL default '0', `orderby` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;-- -- 导出表中的数据 `t_person` -- INSERT INTO `t_person` (`id`, `age`, `height`, `orderby`) VALUES (1, 45, 160, 2), (2, 56, 170, 2);
sql语句要分开写,倒是可以排序。使用用的不就是order by 么?如果只是用sql语句,实现难度太大了,一般是不可能实现的说,必须配合使用程序代码。这几天在看MSSQL,发现这东西里面可以进行自定义函数!这个比较牛啊!比MySQL5.0以前的绝对强多了~(到现在都没有用过MySQL5.0……)
顶上去:如果表结构是这样的怎么办??DROP TABLE IF EXISTS `t_person`; CREATE TABLE IF NOT EXISTS `t_person` ( `id` int(11) NOT NULL auto_increment, `gid` int(11) NOT NULL default '0', ///////////////////////////// `age` smallint(3) NOT NULL default '0', `height` smallint(3) NOT NULL default '0', `orderby` int(11) NOT NULL default '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;现在改成只需读取就行了,不要保存结果了. 怎么改?? SET @rownum=0; SELECT *,@rownum:=@rownum+1 as rownum FROM t_person ORDER BY age ASC, height DESC group by gid;
十万条记录的话 子查询跟全部读出用PHP处理哪个性能好?
CREATE TABLE [dbo].[TempTbl] ( OrderId Int identity(1,1) Not null, --自动排序号 ID Int Not null, --t_person ID CONSTRAINT pk_TempTbl PRIMARY KEY CLUSTERED (ID) WITH FILLFACTOR = 100 ON [PRIMARY], CONSTRAINT ix_TempTbl UNIQUE NONCLUSTERED (ID) WITH FILLFACTOR =100 ON [PRIMARY] ) ON [PRIMARY] GO insert into TempTbl(ID) select ID from t_person order by age asc,height asc update tp set tp.orderby=tt.orderid from t_person tp inner join TempTbl tt on tp.id=tt.id drop table TempTbl
SELECT *,@rownum:=@rownum+1 as rownum FROM t_person ORDER BY age ASC, height DESC;只能能写到这个程度,抛砖....
UPDATE t_person p SET `orderby`=
(SELECT rownum FROM
(SELECT *,@rownum:=@rownum+1 as rownum FROM t_person ORDER BY age ASC, height DESC)
AS r WHERE r.id = p.id)
ORDER BY age ASC, height DESC也够麻烦的了
You can't specify target table 't_person' for update in FROM clause
--
-- 表的结构 `t_person`
-- DROP TABLE IF EXISTS `t_person`;
CREATE TABLE IF NOT EXISTS `t_person` (
`id` int(11) NOT NULL auto_increment,
`age` smallint(3) NOT NULL default '0',
`height` smallint(3) NOT NULL default '0',
`orderby` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;--
-- 导出表中的数据 `t_person`
-- INSERT INTO `t_person` (`id`, `age`, `height`, `orderby`) VALUES
(1, 45, 160, 2),
(2, 56, 170, 2);
这是刚开始支持子查询啊,应该可以啊
CREATE TABLE IF NOT EXISTS `t_person` (
`id` int(11) NOT NULL auto_increment,
`gid` int(11) NOT NULL default '0', /////////////////////////////
`age` smallint(3) NOT NULL default '0',
`height` smallint(3) NOT NULL default '0',
`orderby` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;现在改成只需读取就行了,不要保存结果了.
怎么改??
SET @rownum=0;
SELECT *,@rownum:=@rownum+1 as rownum FROM t_person ORDER BY age ASC, height DESC group by gid;
子查询跟全部读出用PHP处理哪个性能好?
(
OrderId Int identity(1,1) Not null, --自动排序号
ID Int Not null, --t_person ID
CONSTRAINT pk_TempTbl PRIMARY KEY CLUSTERED (ID) WITH FILLFACTOR = 100 ON [PRIMARY],
CONSTRAINT ix_TempTbl UNIQUE NONCLUSTERED (ID) WITH FILLFACTOR =100 ON [PRIMARY]
) ON [PRIMARY]
GO
insert into TempTbl(ID) select ID from t_person order by age asc,height asc
update tp set tp.orderby=tt.orderid from t_person tp inner join TempTbl tt on tp.id=tt.id
drop table TempTbl