根据MySQL documentation,在GROUP BY之后添加ORDER BY NULL应该“避免排序结果的开销”.如果是这样,为什么跟随查询aprox慢5倍SELECT COUNT(*) advert_count, category.name FROM advert LEFT JOIN category ON advert.category_id = category.category_id WHERE ( advert.state_id = 2 ) GROUP BY advert.category_id ORDER BY NULL LIMIT 5 使用ORDER BY advert_count查询？SELECT COUNT(*) advert_count, category.name FROM adv...
我有两张桌子.上传者 – id,名称,图标,视图视频 – ID,上传者,视频,观看次数 上传者列用作视频上传者的参考.所以,我可以使用sum()函数来计算每个用户从他的所有视频中总共有多少视图.现在我想通过desc总视图来订购这些用户. 我该怎么做呢？继承了我到目前为止所拥有的代码……//Retrieve data from the database $fkbar = mysql_query("SELECT * FROM freaks"); while($fkrow = mysql_fetch_array($fkbar))//Echo Theme Template...
我有以下查询：SELECT fruit.date,fruit.name,fruit.reason,fruit.id,fruit.notes,food.name FROMfruitLEFT JOINfood_fruits AS ff ON fruit.fruit_id = ff.fruit_id AND ff.type='fruit'LEFT JOINfood USING (food_id)LEFT JOINfruits_sour AS fs ON fruits.id = fs.fruit_id WHERE(fruit.date < DATE_SUB(NOW(), INTERVAL 180 DAY))AND (fruit.`status` = 'Rotten')AND (fruit.location = 'USA')AND (fruit.size = 'medium')AND ...
我有一个SQL查询如下select * from incidents where remote_ip = '192.168.1.1' and is_infringement = 1 order by reported_at desc limit 1;此查询此刻需要313.24秒才能运行. 如果我删除订单,那么查询是select * from incidents where remote_ip = '192.168.1.1' and is_infringement = 1然后它只需要0.117秒才能运行. reports_at列已编制索引. 所以有两个问题,首先是为什么这个order_by声明收到这么长时间,其次是如何加快它？ 编...
我想在对它们进行分组之前对结果进行排序,但我似乎无法完成它. 我有这个问题：SELECT DISTINCTproduct.id,product_number_value.att_value AS product_number,color_number_value.option_code AS color_number,size_value.option_code AS size_code,size_value.option_position AS size_position FROMproduct INNER JOIN product_attribute_relational AS product_number_relational ON product_number_relational.product_id = pr...
我有一个包含以下数据集的表id name parent ------------------ 1 xyz NULL 2 def NULL 3 mno abc 4 abc NULL 5 pqr abc 6 qfx def我想按以下顺序获取数据：1 abc NULL 3 mno abc 5 pqr abc 2 def NULL 6 qfx def 4 xyz NULL我的意思是ORDERing必须通过用户名进行,但具有相同父级的记录必须一起显示在父级的记录下方. 我正在使用MySQL.解决...
我有2张桌子：create table advertised_products(id int,title varchar(99),timestamp timestamp); insert advertised_products select 1,'t1',curdate();create table wanted_products(id int,title varchar(99),timestamp timestamp); insert wanted_products select 1,'t1',now();我正在使用此查询来获取记录：( SELECT ap.*,'advertised' as type FROM advertised_products as ap ) union all ( SELECT wp.*,'wanted' as typ...
我有这样的查询：SELECT products.* FROM products WHERE products.code = 'search word' OR products.id IN(SELECT product_names.product_id FROM product_names WHERE MATCH (product_names.name) AGAINST ('+search* +word*' IN BOOLEAN MODE) ORDER BY product_names.sorting_field) ORDER BY ???我希望主查询的顺序与WHERE IN()的顺序相同.我似乎无法使用FIELD或FIND_IN_SET,因为我不知道我从MATCH AGAINST回来了什么. 我尝试...
我需要帮助来优化此查询.SELECT messages.*FROM messagesINNER JOIN subscription ON subscription.entity_id = messages.entity_idWHERE subscription.user_id = 1ORDER BY messages.timestamp DESC LIMIT 50没有限制,此查询返回200K行并运行大约1.3 – 2秒.问题似乎在order by子句中.没有它,查询需要.0005秒.Indexes:( subscription.user_id, subscription.entity_id )( subscription.entity_id )( messages.timestamp )( mess...
我有一个表data_summaries.它有诸如item_id INT(11),user_grouping TEXT和值DECIMAL(10,2)之类的列. 如果我尝试通过user_grouping对结果进行分组并按AVG值对它们进行排序,则会失败：SELECT user_grouping, AVG(value) AS avg_value, SUM(value) AS sum_value FROM data_summaries GROUP BY user_grouping ORDER BY avg_value +---------------+-----------+-----------+ | user_grouping | avg_value | sum_value | +-------------...
我想从我的数据库中随机选择一个条目,但不是每次通话,而是取决于一些其他外部因素,如一年中的一周,或一年中的某一天.我怎样才能做到这一点？ 这将告诉我当前的一周：SELECT DATE_FORMAT(CURDATE(),'%u');因此,要获得基于此值的条目,我选择条目号：SELECT (DATE_FORMAT(CURDATE(),'%u')+0) MOD (SELECT COUNT(*) FROM my_view ORDER BY id);所以,我的尝试：SELECT * FROM my_view LIMIT (SELECT (SELECT DATE_FORMAT(CURDATE(),'%...
您好我有Rails 3.2和订购的问题. 当想要按字段订购集合时,调用.last ActiveRecord表现得很奇怪……>> User.order("FIELD(id, '1')")User Load (0.4ms) SELECT `users`.* FROM `users` ORDER BY FIELD(id, '1')=> []>> User.order("FIELD(id, '1')").firstUser Load (0.4ms) SELECT `users`.* FROM `users` ORDER BY FIELD(id, '1') LIMIT 1=> nil>> User.order("FIELD(id, '1')").lastUser Load (0.3ms) SELECT `users`.* FROM ...
这对你来说很简洁(显然是MySQL)：# Setting things up DROP DATABASE IF EXISTS index_test_gutza; CREATE DATABASE index_test_gutza; USE index_test_gutza;CREATE TABLE customer_order (id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,invoice MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,PRIMARY KEY(id) ); INSERT INTO customer_order(id, invoice)VALUES(1, 1),(2, 2),(3, 3),(4, 4),(5, 5);CREATE TABLE customer_invoice (i...
我有一个名为role的列的表,值如下：Scientific staff PostDocs Supporting staff PNUT Visiting researchers Secretary Ph.D. students Students Other我想以科学人员第一的方式使用ORDER BY.在我这样做查询的那一刻,这些领域是博士.学生将首先返回(因为目前数据库中的行没有包含其他字段的字段).有没有办法只使用mysql来实现这一点,还是应该手动修改返回值？如果是的话,你能告诉我怎么样吗？SELECT * FROM members ORDER BY role解...
在我的笔记本上，运行如下 sql代码（总共数据行约7万行，无索引） select * from( SELECT nodeinfo.nodeID nodeid,nodeinfo.niid niid ,nodeinfo.type type ,nodeinfo.testType testtype,sensordatapacket.storedtime storedtime,sensordatapacket.value value, sensordatapacket.unit unit,sensordatapacket.dataType datatype,nodeconfig.nodeAddress nodename FROM nodeinfo Left Join sensordatapacket ON nodei...