This是我的小提琴. 表和数据是create table Table3 (MatchID varchar(10), ItemType varchar(10)); insert into Table3 values ('M001','Fruit'), ('M001','Animal'), ('M002','Fruit'), ('M002','Vehicle');当您有一个按MatchID和ItemType排序的选择查询时,它将返回select MatchID,ItemType from Table3 order by MatchID,ItemType;MATCHID ITEMTYPEM001 AnimalM001 FruitM002 FruitM002 Vehicle像这样,这是预期和正确...
我有以下查询主要工作,但当其中一个连接表返回的结果数不同时,在group_concat()中返回太多结果：select a.sku, a.ek, a.mwst, concat('[[', group_concat('{"offer": ', b.offer, ', "minQuantity": ', b.minQuantity, '}') , ']]') offersA,concat('[[', group_concat('{"offer": ', c.offer, ', "minQuantity": ', c.minQuantity, '}') , ']]') offersB,concat('[[', group_concat('{"offer": ', d.offer, ', "minQuantity": ', ...
我有一张看起来像这样的表： 这个表包含343行. 我正在尝试对它运行此查询：create table newTest2 select function_name, service_name, min(concurrency), substring_index(group_concat(date order by concurrency ), ',',1) as minDate, max(concurrency), substring_index(group_concat(date order by concurrency desc), ',',1) as maxDate , avg(concurrency)from conc_intermidategroup by function_name,service_name;当我...
我有一张桌子id diary 1 breakfast 2 walk 3 start 4 office works 5 office projects 6 end 7 taxi 8 start 9 preparing for meeting 10 doing the meeting 11 end 12 night我想在产品表的开始和结束之间进行CONCAT行：id type diary 1 breakfast 2 walk 3 concated office works, office projects 7 taxi 8 concated preparing for meeting, doing the m...
我有一个规范化的数据库,我正在尝试使用JOIN和GROUP_CONCAT从多个表返回数据. 问题：使用GROUP_CONCAT复制行.我不能使用DISTINCT,因为某些数据(成分mfr)确实需要重复. 这是我当前的查询和数据库结构(SQL Fiddle)：SELECT recipe.*, GROUP_CONCAT(recipe_detail.ingredient_id) AS iid, GROUP_CONCAT(ingredient.name) AS iname, GROUP_CONCAT(ingredient_mfr.abbr) AS mabbr, GROUP_CONCAT(recipe_tag.name) AS tag FROM rec...
数量是一个int.这个SQL有效,但我想总结qty值,而不是连接它们.没有GROUP_SUM：被称为别的东西？SELECT sku as filterSKU, storenumber as storenumberSKU,GROUP_CONCAT((CASE weekdayoftransaction WHEN 0 THEN qty ELSE NULL END)) AS Monday,GROUP_CONCAT((CASE weekdayoftransaction WHEN 1 THEN qty ELSE NULL END)) AS Tuesday,GROUP_CONCAT((CASE weekdayoftransaction WHEN 2 THEN qty ELSE NULL END)) AS Wednesday,GROUP_C...
所以这是我的SQLSELECT c.name AS 'name',COUNT(sc.condition_id) AS 'conditions_count' FROM reviews r, strain_conditions sc LEFT JOIN conditions c ON sc.condition_id = c.id WHERE sc.strain_id = 1 && sc.review_id = r.id && r.moderated = 1 GROUP BY sc.condition_id ORDER BY conditions_count DESC LIMIT 3;哪个回报+---------------+------------------+ | name | conditions_count | +---------------+--...
我有以下查询：select card.id as id, photo.image as photo from card left outer join card_image as photo on (photo.card=card.id)返回+----+-------+ | id | photo | +----+-------+ | 1 | 2 | | 1 | 3 | | 1 | 4 | | 2 | 5 | | 2 | 6 | +----+-------+如果我将photo.image作为照片更改为group_concat(photo.image)作为照片,我会得到以下结果+----+---------------+ | id | photo | +----+-...
SQL小提琴：http://sqlfiddle.com/#!9/e7f72/2 假设一个名为testt的表中有10条记录(id不是null自动递增).如果我这样做的话SELECT GROUP_CONCAT(id) FROM testt我希望结果看起来像 1,2,3,4,5,6,7,8,9,10 我怎么能得到这样的结果： 1,2 3,4 5,6 7,8 9,10解决方法:您需要按ID的功能进行分组.像这样的东西：select group_concat(id order by id) from testt group by floor((id - 1) / 2)
数据：id uid type1 20 A 2 20 B 3 20 A 4 6 A 5 1 A 6 3 A 7 6 A 8 1 B场景： 我想按类型分组并按ID排序.我正在使用group by来分组uid. 当前查询：SELECTtype,GROUP_CONCAT(DISTINCT uid) AS users,COUNT(type) AS typeCount FROM`test2` GROUP BYtype问题： 但是uid的顺序不正确,它应该按照id的降序排列. 预期结果：type users typeCount A 6...
SELECT A.id, A.title, FROM (`table`) as A WHERE A.active = '1' AND A.id IN (SELECT GROUP_CONCAT(B.id) from B where user = 3)如果我从B中启动子查询SELECT GROUP_CONCAT(B.id),其中user = 3,我获得1,2,3,4.但是,如果我启动整个查询,我只获得一行. 但是如果我试图用它的值替换子查询(1,2,3,4)SELECT A.id, A.title, FROM (`table`) as A WHERE A.active = '1' AND A.id IN (1,2,3,4)我获得了4行……正如我所需要的那样. ...
是否可以在Group_concat中使用分隔符逗号进行选择并用逗号搜索concat这是我的mysql示例：products_attributes_id |products_id| options_id| options_values_id 39 | 31 | 3 | 3 35 | 30 | 2 | 2 38 | 30 | 1 | 1 40 | 31 | 2 | 2 ...
我有2个mysql表：car_model:id (int) (Primary Key)title (varchar)id_brand (int) FK to car_brand tablecar__car_model: - relation many to manyid_model (int) id_advice_model (int)在car__car_model中有以下数据：(id_model) (id_advice_model) 12 12 12 45 12 67 12 78 13 13 13 12 13 67 13 105我想要像这样获取这些数据：12 12,45,67,78 13...
我不明白为什么这个GROUP_CONCAT不起作用,就外部查询而言,返回了3行所以我想通过它来组合group_concat但它不喜欢它… http://sqlfiddle.com/#!2/24764/3CREATE TABLE nested_category (category_id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(20) NOT NULL,lft INT NOT NULL,rgt INT NOT NULL );INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),(4,'LCD',5,6),(5,'PLASMA',7,8...
最近遇到一种情况： 要展示操作日志，按照时间顺序排列。因为我记录的时候呢，是每一个小操作记录一条，而一次提交可能有好几个小操作。这样查询的时候，如果全列出来，那真是好长一列，还是把相同时间下的操作整合一下比较好。 但是如果使用 group by 语句，查询结果只显示每一组的一条记录，并不符合需求。难道我要自己处理吗？想想就头大，然后我就发现了 GROUP_CONCAT 分组查询配合GROUP_CONCAT()来使用，可以看到每个组中的详...