你的问题有多个部分。如果你想要 0 值,则需要外部联接或相关子查询。我想你有一张所有比赛的桌子,但是 select distinct 上 entries 桌子也能用。

如果你想让大多数人崩溃 每辆车 你需要一个 汽车 中的标识符 crashes 表。我肯定有一个(这很有道理),但你不会在问题中描述它。我猜大概是 c_raceEntryCarId .

结果查询如下所示:

select e_racename, e.e_racedate,
       (select count(*)
        from crashes c
        where c.c_racename = e.e_racename and c.e_racedate = e.e_race_date 
        group by c.c_raceEntryCarId
        order by count(*) desc
        limit 1
       ) as most_crashes
from (select e.e_racename, e.e_racedate
      from entries e
      group by e.e_racename, e.e_racedate
     ) e;

如果您只想知道每次比赛的崩溃次数,那么子查询将有所不同:

select e_racename, e.e_racedate,
       (select count(*)
        from crashes c
        where c.c_racename = e.e_racename and c.e_racedate = e.e_race_date
       ) as most_crashes
from (select e.e_racename, e.e_racedate
      from entries e
      group by e.e_racename, e.e_racedate
     ) e;

在MySQL中,可以使用相关的子查询:

select l.*
from locations l
where l.id = (select l2.id
              from locations l2
              where l2.name = l.name
              order by (feature_code = 'RGN') desc,  -- put regions first
                       id asc
             );

在MySQL 8 +中,也可以使用 row_number() :

select l.*
from (select l.*,
             row_number() over (partition by name 
                                order by (feature_code = 'RGN') desc, id
                               ) as seqnum
      from locations l
     ) l
where seqnum = 1;

我认为这是可行的:

SELECT ka.kodeagent
       (SELECT COUNT(1)
        FROM bsn_data d
        WHERE d.periode >= '2018-12-01' AND
              d.periode < '2019-01-01' AND
              d.kodeupline2 = ka.kodeagent AND
              d.kodeagent = (SELECT d2.kodeagent
                             FROM bsn_data d2
                                  d2.periode >= '2018-12-01' AND
                                  d2.periode < '2019-01-01' AND
                                  d2.kodeupline2 = ka.kodeagent
                             GROUP BY d2.kodeagent
                             ORDER BY COUNT(1) DESC
                             LIMIT 1
                            )
        ) AS totps
FROM bsn_kode_agent ka
WHERE ka.fungsi = 'sales agent'
ORDER BY totps DESC;

笔记:

• 不要在日期上使用字符串操作!使用正确的日期操作。
• 使用表别名并限定表名。
• = 可以使用 limit 虽然,虽然 in 不能。
• COUNT() 不回来了 NULL ,所以不需要 无效的 比较。

我仍然认为这个查询不起作用,因为您有一个双重嵌套的correlation子句。但这确实解决了你眼前的问题。

如果还是不行,就问 另一个 提出问题,提供示例数据、所需的结果,并解释要实现的逻辑。

你为什么要用循环来做这个?只做:

 INSERT INTO `forum_topicresponder` (topic_id, username, reply_id)
     SELECT p.topic_id, p.creator_name, p.id
     FROM forum_post p
     WHERE p.topic_id <= 665;  -- or use a variable if you prefer

您的查询只是一个错误的查询,因为您正在使用 select * 具有 group by . 此查询使用MySQL扩展。而且,mysql最新版本中的默认设置将生成错误。

下面是一种将此转换为SQL Server的方法:

SELECT TOP (1) WITH TIES * 
FROM Table1 AS T1 INNER JOIN
     Table2 AS T2 
      ON T1.Column1 = T2.ColumnX 
WHERE T1.ColumnY = 'xxxx' 
ORDER BY ROW_NUMBER() OVER (PARTITION BY T1.Column1 ORDER BY (SELECT NULL)) ;

可能更好的方法(从性能角度)是使用横向连接:

SELECT * 
FROM Table1 T1 CROSS APPLY
     (SELECT TOP (1) T2.*
      FROM Table2 T2 
      WHERE T1.Column1 = T2.ColumnX 
     ) T2
WHERE T1.ColumnY = 'xxxx' ;

这两个选项都从中选择任意行 Table2 当比赛不止一场时。

我只想用 exists :

SELECT c.cl_id , c.cl_name_last 
FROM vt_clients c 
WHERE EXISTS (SELECT 1
              FROM vt_animals a
              WHERE c.cl_id = c.cl_id AND
                    a.an_type NOT IN ('snake', 'lizard', 'turtle')
            );

你只需要一种非爬行动物的存在,所以没有必要 count() .

我想到了四个选择。首先是在应用程序中执行所有检查。可行,但不是你所要求的。

第二个是为 insert 和 update . 他们将拒绝在前10个月之后的任何额外任命。

第三个是10列,每个约会时段一列。

第四种选择是预先填充一个日历表,其中的槽位于单独的行中。然后更新所需的插槽。

我绝对不是第三选择的粉丝,但我会用它来回答你的问题。

我倾向于选择第四种或第一种。

可以在中使用表达式 order by . 所以:

order by (case when title like concat('%', @search_term, '%') then 1 else 2 end),
         (case when description like concat('%', @search_term, '%') then 1 else 2 end)

在mysql中,这可以简化为:

order by (title like concat('%', @search_term, '%')) desc,
         (description like concat('%', @search_term, '%')) desc

您还可以为此使用相关子查询:

select lt.*
from temp_laptimes lt
where lt.laptime = (select min(lt2.laptime)
                    from temp_laptimes lt2
                    where lt2.lapdate <= lt.lapdate
                   );

但是,gmb提出的累积窗口函数在mysql 8+中表现得更好。

我想一个索引 ActivityX(user, ActivityNum) 会解决你的问题。

我猜你有个索引 (ActivityNum) 优化器正试图找出是否应该使用索引。这会导致阈值。复合索引应该更好地匹配查询。

这很痛苦,但您可以使用变量和聚合来完成:

select max(doctor) as doctor,
       max(engineer) as engineer,
       max(developer) as developer,
       max(musician) as musician
from ((select name as doctor, null as engineer, null as developer, null as musician,
              (@rnd := @rnd + 1) as rn
       from t cross join
            (select @rnd := 0) as params
       where occupation = 'doctor'
      ) union all
      (select null as doctor, name as engineer, null as developer, null as musician,
              (@rne := @rne + 1) as rn
       from t cross join
            (select @rne := 0) as params
       where occupation = 'engineer'
      ) union all
      (select null as doctor, null as engineer, name as developer, null as musician,
              (@rnv := @rnv + 1) as rn
       from t cross join
            (select @rnv := 0) as params
       where occupation = 'developer'
      ) union all
      (select null as doctor, null as engineer, null as developer, name as musician,
              (@rnm := @rnm + 1) as rn
       from t cross join
            (select @rnm := 0) as params
       where occupation = 'musician'
      ) union all
     ) o
group by rn;

你似乎想要:

select t.*
from titles t
where not exists (select 1
                  from bannedwords bw
                  where t.title not like '%' + bw.name + '%'
                 );

当然,这个逻辑也会排除任何带有“essex”或“sussex”或“middlesex”或“sextuplet”等的标题。

这就是你想要的吗?

select b.GNO, b.NAME, b.DATE, s.YEAR, s.NAME, s.AGE
from boxes b left join
     advstat s
     on b.NAME = s.NAME and year(b.date) = s.year
where b.GNO = 1
group by b.NAME, b.GNO ;

注意,在 group by 通常效率更高,所以我建议 where 而不是 having .

一种方法是关联子查询:

select t.*
from t
order by (select max(t2.publish_date)
          from t t2
          where t2.author = t.author
         );

mysql 8+(和标准sql)使用窗口函数有一个简单得多的方法:

select t.*
from t
order by max(publish_date) over (partition by author)

你可以用 having 从句 group by . 你的问题有点模棱两可。如果你想要只有一个成员 position 价值 :

SELECT member
FROM t
GROUP BY member
HAVING MIN(position) = MAX(position);

如果希望成员/职位组合只出现一次:

SELECT member, position
FROM t
GROUP BY member, position
HAVING COUNT(*) = 1;

它不优雅,但您可以做到:

SELECT s.sensor_1 
FROM sensor_table s
WHERE s.timestamp >= UNIX_TIMESTAMP($date_start) AND
      s.timestamp < UNIX_TIMESTAMP($date_stop) AND
      s.timestamp = (SELECT MIN(s2.timestamp)
                     FROM sensor_table s2
                     WHERE s2.timestamp >= 60 * 10 * FLOOR(UNIX_TIMESTAMP(s.timestamp) / (60 * 10)) AND
                           s2.timestamp < s2.timestamp >= 60 * 10 * (1 + FLOOR(UNIX_TIMESTAMP(s.timestamp) / (60 * 10)))
                    )
ORDER BY timestamp;

这将选择每10分钟中的第一个。

如果最大密钥长度为255字节,并且您使用的是UTF8编码,那么每个字符最多可以是4个字节。

因此,最大为63字节。这项工作:

CREATE TABLE tbl (
    idx TINYTEXT,
    PRIMARY KEY (idx(63))
);

首先,这通常是使用聚合编写的:

DELETE p
    FROM Person p INNER JOIN
         (SELECT p2.email, MIN(p2.id) as min_id
          FROM Person p2
          GROUP BY p2.email
         ) p2
         ON p.email = p2.email and p.id > p2.min_id;

为什么你的版本有效?好吧,这是因为 join 不仅匹配数据,而且过滤数据。

所以,情况

t1.Email = t2.Email and t1.Id > t2.Id

对每一张唱片都这么说 t1 在t2中查找匹配的记录,其中 t1.id > t2.id . 也就是说,在 t1 有匹配的记录 较小 id .

所有记录都有这个属性——除了每个电子邮件有一个。那将是ID最小的记录。

我不建议使用这种方法来识别最小的记录,因为 参加 乘以记录数。如果一封电子邮件有五个记录,那么其中一个记录最多有四个匹配项。当你说要删除一个记录四次时,MySQL需要弄清楚该怎么做。(当然,这是正确的做法,但还有额外的工作。)

聚合方法没有类似的问题。

只需在单个查询中写入:

SELECT submission_id, 
       MAX( CASE field_id WHEN '1' THEN value ELSE '0' END ) as Usernumber,
       MAX( CASE field_id WHEN '2' THEN value ELSE '0' END ) as Company, 
       MAX( CASE field_id WHEN '3' THEN value ELSE '0' END ) as Zipp, 
       MAX( CASE field_id WHEN '5' THEN value ELSE '0' END ) as Partnernumber 
FROM table
GROUP BY submission_id;

这些视图并不能真正帮助您处理这个查询。

编辑:

根据您的评论:

select *
from view1 v1 join
     view2 v2
     using (Usernumber);

我相信这回答了你的问题,尽管有点间接。

这些比较看起来非常可疑:

WHERE post_id = '$postname' AND user_id = '$username'

不仅仅是因为SQL注入的可能性。比较 id s-to-name通常是不正确的逻辑——所以失败的比较意味着什么都不会更新。当然,您应该使用参数,但这是另一回事。

我怀疑你需要某种 JOIN 在您的查询中。但没有样本数据和期望的结果,这只是猜测。

你在找这样的东西吗?

SELECT BRAND_NAME AS AVG_BRAND, AVG(PROD_PRICE) AS AVG_PRICE
FROM LGPRODUCT PROD JOIN
     LGBRAND BRAND 
     ON PROD.BRAND_ID = BRAND.BRAND_ID
GROUP BY PROD.BRAND_ID
ORDER BY AVG_PRICE DESC
LIMIT 1;

使用A join 派生表或临时表:

SELECT n.n, t.id, MOD(t.column_name, n.n) AS mod_output
FROM table t CROSS JOIN
     (SELECT 4 as n UNION ALL SELECT 5 UNION ALL SELECT 6 . . .
     ) n
WHERE MOD(t.column_name, n.n) > 10;

如果希望结果作为列,可以在之后使用条件聚合。

您的查询过于复杂。聚合不需要子查询。但是,有必要使用变量:

SELECT month, year, total_price,
       (@running_total := @running_total + total_price) as running_totals
FROM (SELECT MONTHNAME(l.app_date) as month,
             YEAR(l.app_date) as year,
             MONTH(l.app_date) as mon,
             COUNT(DISTINCT l.loan_id) as contracts,
             SUM(l.price * l.term) total_price
      FROM _loans l
      GROUP BY l.loan_id, YEAR(w.app_date), MONTH(w.app_date)
      ORDER BY l.loan_id, YEAR(w.app_date), MONTH(w.app_date)
     ) l CROSS JOIN
     (SELECT @running_total := 0) params
ORDER BY year, mon ASC