MYSQL查询语句遇到的一些问题，求解决！

麻烦各位大lao帮我看下我的sql查询语句：

使用场景：四张表， 用户表，设备表，设备的子设备表，塘口表

需要传入地址，针对地址查询符合条件的设备，然后使用符合条件的设备所属用户，进行他们的数据统计

查询后的结果是：用户信息，用户设备数量、用户子设备数量、用户鱼塘数量无误

遇到的问题：针对设备数量查询，再加一个设备时间小于当前时间的条件，查询的数量不对，设备信息不能指定获取哪一台的数据，
劳烦各位大lao帮我看一下

SELECT
    m.id,
    m.nickname,
    m.mobile,
    m.emergency_call,
    m.spare_phone,
    m.add_time,
    COUNT( DISTINCT d.id ) AS 'device_count',
    COUNT( DISTINCT ds.id ) AS 'device_sensor_count',
    COUNT( DISTINCT f.id ) AS 'fishpond_count',
    COUNT(
        DISTINCT d.id 
    AND d.combo_pack_end_time < NOW()) AS 'state',
    d.address,
    d.city 
FROM
    member m
    LEFT JOIN device d ON m.id = d.user_id
    LEFT JOIN device_sensor ds ON m.id = ds.member_id
    LEFT JOIN fishpond f ON m.id = f.user_id and f.deleted = 0
WHERE
    m.id IN (
    SELECT DISTINCT
        m.id 
    FROM
        device d
        INNER JOIN member m ON d.user_id = m.id 
        AND d.deleted = 0 
    WHERE
        d.province = '广东省' 
    ) 
GROUP BY
    m.id

@

你的SQL查询语句中，对设备数量的查询添加了一个设备时间小于当前时间的条件，但是这个条件是在COUNT函数内部的，这样的写法是不正确的。你应该将这个条件放在JOIN或者WHERE子句中。

另外，你提到设备信息不能指定获取哪一台的数据，这是因为你在GROUP BY子句中按照用户ID进行了分组，所以只能获取到每个用户的设备数量，而不能获取到具体的设备信息。如果你想获取具体的设备信息，你可能需要修改你的查询语句，或者进行多次查询。

以下是我对你的SQL查询语句的修改建议：

SELECT
    m.id,
    m.nickname,
    m.mobile,
    m.emergency_call,
    m.spare_phone,
    m.add_time,
    COUNT( DISTINCT d.id ) AS 'device_count',
    COUNT( DISTINCT ds.id ) AS 'device_sensor_count',
    COUNT( DISTINCT f.id ) AS 'fishpond_count',
    COUNT( DISTINCT CASE WHEN d.combo_pack_end_time < NOW() THEN d.id END ) AS 'state',
    d.address,
    d.city 
FROM
    member m
    LEFT JOIN device d ON m.id = d.user_id
    LEFT JOIN device_sensor ds ON m.id = ds.member_id
    LEFT JOIN fishpond f ON m.id = f.user_id and f.deleted = 0
WHERE
    m.id IN (
    SELECT DISTINCT
        m.id 
    FROM
        device d
        INNER JOIN member m ON d.user_id = m.id 
        AND d.deleted = 0 
    WHERE
        d.province = '广东省' 
    ) 
GROUP BY
    m.id

在这个修改后的查询语句中，我使用了CASE WHEN语句来处理设备时间小于当前时间的条件。这样，只有当设备时间小于当前时间时，设备ID才会被计入state的计数中。

希望这个修改后的查询语句能够解决你的问题。如果还有其他问题，欢迎继续提问。

• 这个问题的回答你可以参考下: https://ask.csdn.net/questions/730155
• 除此之外, 这篇博客: mysql中主键索引、唯一键索引、全局索引、普通索引详解中的 因为本人之前一直写的是电子笔记，对自己学会的东西作一个总结，所以基本都是文字，本来想全发成博客的形式，发现全发成博客比较花费时间，而且一直发博客质量不是很好，而且通过发博客学到的东西也会变少，所以准备先把笔记发出来，后续再将它们改成博客的形式，争取2天至少改一篇博客，觉得我总结的还行的可以先关注我，后续会发成博客形式，内容也会更加完善 部分也许能够解决你的问题, 你可以仔细阅读以下内容或跳转源博客中阅读:
  

• **

  索引：
  索引就是用来提高数据库性能的东西，不用加内存，不用改程序，不用调sql，只要执行正确的
  搜索代码 ，查询速度就可能提高成百上千倍，但是查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO，所以它的价值，在于提高一个海量数据的检索速度
  索引就像书的目录，可以通过目录中的页码快速找到我们所需要的内容，而数据库中被指定为索引的字段，就是像是一种目录，一般情况下这种索引的结构是用二叉树来构建的，通常提高插入或删除的时间来提高查找的效率，说白了索引就是一种在查询数据时，节约时间的东西，例如之前我要查找一个数据需要10秒，当我给这个字段加上索引后，它可能只需要0.1秒就能找到 那个数据
  主键索引：
  主键是用来约束一个数据表的，它是一个索引，被设置成主键的字段，不能为空，并且此字段下的数据不能重复，也就是说如果我给names字段设置了主键，那么这个字段下的数据就不能重复出现，而且names字段中的数据不能设置为NULL，这在很多地方是有用的，有时我们希望一些字段，不会有相同的数据，例如学号，每个学生的学号都不同，而且主键用来检索也是很方便的，并且主键只能设置一个，也就是说一张表只能有一个主键，如想让有一个表有多个主键，可以使用复合键，总之在设置一次主键后，除非删除掉所有主键，否则后续不 能再设置主键，设置主键的方法是，在要设置主键的字段类型后，空一格输入primary key就可以了，如果要设置复合键，可以在最后面输入primary key（）括号里面是要设置成主键的字段名，字段名之间用逗号隔开；例如我要设置names为主键，就可以这样写alter table stud modify names varchar(32) primary key;在数据表结构中用key字段来表示某个字段是否设置了主键，如果设置了主键会显示PRI
  
  

     如果要在创建数据表时设置主键，可以这样写create table test(id int primary key, name varchar(30));如果要使用复合键，可以这样写create table user2(id int, name varchar(30), primary key(id, name));
     如果想要删除一个数据表的主键可以使用drop命令，使用方法为alter table 数据表名 drop primary key，例如我要删除names的主键属性，可以使用alter table stud drop primary key;
  

  
  唯一键索引：
  因为一张数据表可能有多个字段需要设置数据不能重复，但是主键只能有一个，当我们需要添加一列，并想要把这一列设置为数据不能重复时，就没有办法了，因为之前已经设置过一次主键了，这时我们就可以使用唯一键，来设置这一列的属性，唯一键也是一种索引，唯一键和主键的功能差不多一样，区别就是，唯一键允许这个字段的数据为空（也就是可以不给设置过唯一键的字段添加数据，如果有好几个数据为空，那么不会比较这几个数据的唯一性，也就是空的数据是可以重复的，如果唯一键设置过not null属性，那么唯一键的作用和主键的作用就是相同的），并且唯一键可以设置多个，如果要设置唯一键，就要用unique命令，使用方法是，在字段的类型后面加上unique，例如我要设置age为唯一键，可以这样写alter table stud modify age int unique;然后在表的结构中KEY字段的对应位置就会出现UNI，表示这个字段设置过唯一键（在已有的列上设置唯一键时，要保证这一列没有重复的数据）
  
  

     在创建表时也可以设置唯一键，create table test(id int primary key, name varchar(30) unique);或者也可以在表的最后设置唯一键，例如：create table test(id int primary key, name varchar(30), unique(name));
     如果要删除唯一键，我们可以使用drop命令，如果想删除某一个字段的唯一键，可以使用alter table 数据表名 drop index  字段名，的方法进行删除，其实删除唯一键就是删除唯一键的索引，也就是删除它的指针，例如我要删除age字段的唯一键，可以这样写alter table stud drop index age;
  

  
  普通索引：
  普通索引就是单纯的索引，只是为了让这一列的数据更容易、更快的查询，对此字段的数据并没有什么要求，也就是说设置普通索引的字段数据，可以为空，也可以重复并且一张表中可以设置多个普通索引，设置普通索引的方法是使用index关键词，它设置的方法和唯一键（主键），不太相同，不能直接在要创建索引的字段名后面加index，而是要把它当做一个新成员进行添加，例如有如下一张数据表：
  
  现在我要修改school这个数据的leader字段为普通索引，可以这样写“alter table school add index(leader);”这张表的结构中，Key字段会在对应位置出现MUL，表示对应的列设置过普通索引
  
  也可以在创建数据表时添加普通索引，例如”create table test(id int primary key, name varchar(20), name varchar(20), index(name));“
  如果要删除普通索引，我们可以使用drop命令，删除的方法是，可以使用alter table 数据表名 drop index 字段名，的方法进行删除，例如我要删除school这个数据表的leader字段的普通索引，可以这样写“alter table school drop index leader;”
  
  全文索引：
  一般的索引是用来帮我们检索字段中的数据的，也就是要使用索引进行查询，就得将添加过索引的数据全部输入，然后去检索，比较适合那些只有少量文本数据的字段，而全文索引是用来做海量数据的查询的，它可以进行模糊匹配，也就是说如果一个字段设置过全文索引，那么它的模糊匹配效率会很高，所以全文索引是专门给字符串之类的字段而设置的，举个例子来说就是，我们有一个字段是text类型，然后每行数据中存入大量的文本，现在我想找出这个字段中，包含”英雄联盟“这个字符串的行，一般情况下我们当然可以使用where语句，然后用like进行模糊匹配，例如，select * from test where game like “%英雄联盟%”，来查询game字段中包含”英雄联盟“这个字符串的行，虽然我们可以这样进行模糊匹配，但是在有大量文本的数据库中，这样的匹配方法的效率是极其慢的，所以增加了全文索引来提高这种模糊匹配的速度，定义全文索引的方法是fulltext，如果要在创建表时就定义全文索引可以这样写create table articles (id int unsigned auto_increment not null primary key, title varchar(200), body text, fulltext (title,body))engine=MyISAM;然后我们插入一些数据，可以看到如下表：
  

     如果要在已有的数据表上，给某个字段增加全文索引字段，可以使用”alter table 数据表名 add fulltext(字段名1， 字段名2 ……);
     在使用全文索引时，要配合match和against关键词使用，例如我要查询包含“database”这个字符串的行，可以使用“select * from articles match(title, body) where against('database');“，注意match中的数据是本数据表中所有设置过全文索引的字段，如果只想查询一个字段，那么只能设置一个字段的全局索引，如果match中的字段和设置过的全局索引不对称，那么就会出错，原因是全文索引不会记录关键词来自哪一行，另外全文索引中会有一个最小搜索长度的概念，因为全文索引是用来处理海量数据的，所以如果数据很少就没有必要使用，全文索引，直接用like就可以了，当然这个最小搜索长度我们也可以自己进行设置（这个设置百度上有很多文章）
  

  
  另外我们进行全文检索时，一般against默认使用的是自然语言的匹配（in natural language mode），也就是说against（“database” in natural language mode)，的完整格式是这样的，还有一种搜索是布尔全文匹配，它是以自然语言加一些特殊的操作符而形成的检索字符串，”+“代表必须包含该字符串，”-“代表必须不包含该字符串，”>“代表提高该字符串的的相关性，”<“代表降低该字符串的相当性，”“代表其它的字符串，只能拼接在该字符串的后面，例如against（"database" in boolean mode）
  可以使用show keys from 数据表名，来查询此数据表是否被设置过全文索引：
  
  最后MySQL提供全文索引机制，但是有要求，要求数据表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文