常用SQL_2

weixin_33850890發表於2018-07-10
SQL

1.like

//like
select count(*) from im_message where msg like "%你好%";

2.delete

delete from im_message where msg ="你好";

3.group by

select a.user_id id , a.domain_username name from im_domain_user a where a.domain_id = 5 group by id;

4.多表

select * from im_message message, im_domain_user user where user.user_id = message.to_user_id and user.domain_id = 5;

5.inner join

select * from im_message message inner join im_domain_user user on user.user_id = message.to_user_id and user.domain_id = 5;
//left join：all A rows （right join：all B rows）
select * from im_message message left outer join im_domain_user user on user.user_id = message.to_user_id;
```java
6.
```java
select count(*) from hmc_customer_info customer left join hmc_consultant consultant on (customer.isBindConsultant != 1) and (consultant.mobilePhone = '15299500912');

7.搶客： 根據置業顧問mobilePhone查詢該樓盤下沒有被搶客的客戶

select count(*) from hmc_customer_info customer where (customer.isBindConsultant != 1) and customer.newHouseId=(select consultant.newHouseId from hmc_consultant consultant where consultant.mobilePhone = '13851782962');

8.簽到：根據置業顧問電話查詢置業顧問id，根據id查詢最後簽到時間，得出時間差

SELECT * FROM hmc_consultant_score cScore where cScore.city = "nj" and cScore.userId = (SELECT consultant.id FROM hmc_consultant consultant where consultant.mobilePhone = '13851782962') order by createTime desc limit 1

9.include

SELECT * FROM hmc_customer_info WHERE mobileNumber in ('13770551213','13770551219') and isBindConsultant != 1;
select id from hmc_consultant where newHouseId in (25,29,37,46,62,68,71,77,83,219,220,221,222,223,224,225,226,227,228) and status = 2;

10.查詢片區積分排名前十

select c.id,c.userName,c.newHouseId,sum(s.score) accumulatePoint  
             from hmc_consultant_score s join hmc_consultant c on s.userId = c.id
         where c.newHouseId = any(select id from hmc_new_house where districtId = (select h.districtId from hmc_new_house h where h.id = (select newHouseId from hmc_consultant where id = 25575)))
         group by c.id order by accumulatePoint desc
            LIMIT 10

SELECT newtable.newHouseId,newtable.newHouseName,sum(accumulatePoint) total
         FROM (SELECT c.id,c.userName,c.newHouseId,c.newHouseName,sum(s.score) accumulatePoint 
         FROM hmc_consultant_score s join hmc_consultant c on s.userId = c.id 
         where c.newHouseId = any(SELECT 
         id from hmc_new_house where districtId = (SELECT 
         h.districtId from hmc_new_house h where h.id = (SELECT 
         newHouseId from hmc_consultant where id = 25575)))
         group by c.id) as newtable where 1=1
         group by newtable.newHouseId order by total desc LIMIT 10;

select * from hmc_consultant_score cscore where cscore.city="nj" and cscore.scoreDetail="簽到積分" and cscore.userId=(select consultant.id from hmc_consultant consultant where consultant.mobilePhone='15950463080') order by createTime desc limit 1;

13.查詢失敗數

SELECT a.opDate,b.id,b.userName,b.mobilePhone,b.shortNumber,b.currScore,b.totalScore FROM(SELECT * FROM hmc_compete_bidding a WHERE a.newHouseId ="234" AND a.city="nj" AND a.status != 1) a LEFT JOIN hmc_consultant b ON a.userId = b.id AND b.city ="nj" ORDER BY a.opDate DESC;

SELECT a.opDate,b.id,b.userName,b.mobilePhone,b.shortNumber,b.currScore,b.totalScore FROM(SELECT * FROM hmc_compete_bidding a WHERE a.newHouseId=#searchFilters.newHouseId.value# AND a.city=#searchFilters.city.value# AND status != 1) a LEFT JOIN hmc_consultant b ON a.userId = b.id AND b.city =#searchFilters.city.value# ORDER BY a.opDate DESC;

SELECT c.deviceid FROM
        hmc_consultan_device c 
        WHERE c.status = 0 AND
        c.uid=any(
        SELECT b.id FROM hmc_consultant b
        WHERE b.status = 2
        and b.newHouseId = (
        SELECT a.id
        FROM hmc_new_house a WHERE a.newHouseId = 167189 ));

SELECT c.opDate,b.id,b.userName,b.mobilePhone,b.shortNumber,b.currScore,b.totalScore FROM(SELECT * FROM hmc_compete_bidding a WHERE a.newHouseId ='234' AND a.city= 'nj' AND a.status = 2 AND a.createTime BETWEEN '2015-10-01 00:00:00' AND '2015-11-12 10:10:00' ) c LEFT JOIN hmc_consultant b ON c.userId = b.id AND b.city = 'nj' ORDER BY c.opDate DESC;

    SELECT
        count(1)
        FROM (SELECT *
        FROM hmc_compete_bidding a
        WHERE a.newHouseId =
        #searchFilters.newHouseId.value# AND a.city
        = #searchFilters.city.value# AND a.status = 2
        AND a.createTime BETWEEN
        #searchFilters.beginTime.value#
        AND #searchFilters.endTime.value# ) c LEFT JOIN
        hmc_consultant b ON c.userId = b.id AND
        b.city = #searchFilters.city.value#
        ORDER BY c.opDate DESC

<!-- 查詢競標置頂失敗詳情列表  -->
    <select id="competeTopDetaiFaillList" resultClass="hmcConsultantVo">
        SELECT c.opDate,b.id
        ,b.userName,b.mobilePhone,b.shortNumber,b.currScore,b.totalScore
        FROM
        (SELECT *
        FROM hmc_compete_bidding a
        WHERE a.newHouseId =
        #searchFilters.newHouseId.value# AND a.city
        = #searchFilters.city.value# AND a.status = 2
        AND a.createTime BETWEEN
        #searchFilters.beginTime.value#
        AND #searchFilters.endTime.value# ) c LEFT JOIN
        hmc_consultant b ON c.userId = b.id AND
        b.city = #searchFilters.city.value#
        ORDER BY c.opDate DESC
        <![CDATA[
            LIMIT #paging.startRecord#, #paging.pageSize#
        ]]>
    </select>
    
    update hmc_consultant set status=2 ,newHouseName="中交錦蘭薈",newHouseId="31",currScore=1000,totalScore=1000，authSuccessTime ='2016-03-10 16:04:03' where mobilePhone in ('18602550608');
    
    update hmc_new_house set newHouseId=117275 where id = 250;
  1. 根據newhouseid和imid查詢置業顧問
    SELECT a.id,a.userName,a.phone,a.gender,a.headImageUrl      ,a.shortNumber,a.newHouseId,a.newHouseName,a.status,a.imid,a.score,a.currScore,MAX(a.opDate)
        opDate ,IFNULL (a.sort,2) sort FROM (
        SELECT a.*,IFNULL(b.score,0)
        score,b.opDate,b.status sort FROM (
        SELECT
        a.*,b.imid FROM (
        SELECT a.* ,
        REPLACE(IFNULL(b.is_valid,1),2,1)
        STATUS
        FROM (
        SELECT
        b.id,b.userName,b.mobilePhone
phone,b.gender,b.headImageUrl,b.shortNumber,b.newHouseId,b.newHouseName,b.currScore,b.regTime
        FROM hmc_new_house a LEFT
        JOIN
        hmc_consultant b ON a.id = b.newHouseId
        WHERE b.status = 2 AND a.newHouseId = 117275 AND a.city = 'nj'
        AND
        ('2015-12-16' BETWEEN a.beginTime AND a.endTime)
        ) a
        LEFT JOIN hmc_auth_token b ON a.id =
        b.uid ) a LEFT
        JOIN im_domain_user b ON a.phone =
        b.phone ) a LEFT JOIN
        hmc_compete_bidding b ON a.id = b.userId
        ) a LEFT JOIN (
        SELECT b.* FROM
        hmc_new_house a INNER JOIN
        hmc_compete_bidding_simple b ON
        a.id =
        b.newHouseId
        WHERE a.newHouseId
        =117275 AND a.city = 'nj' AND
        b.opDate='2015-12-16') b ON a.id
        =
        b.createUserId GROUP BY phone ORDER BY
        STATUS ,sort,score DESC ,currScore DESC ,regTime;

insert into hmc_new_house(
id, newHouseId, newHouseName, pinyin, moduleId, moduleName, districtId, districtName, beginTime, endTime, developer, city, newHouseChannel, createUserId, createUserName, createTime, lastModifyUserId, lastModifyUserName, lastModifyTIme
          )values(
           250,
           117275,
           '中垠紫金觀邸',
           'zhongyinzijinguandi',
           3,'城東',7,'棲霞區',null,null,6812,'nj','住宅',null,null,null,null,null,null
          );
          
insert into hmc_consultant_score(userId,score,scoreDetail,createTime,city)values('26366',1000,"首次認證積分",'2016-01-04 10:07:56','nj');

19.排序

SELECT a.id,a.userName,a.phone,a.gender,a.headImageUrl  ,a.shortNumber,a.newHouseId,a.newHouseName,a.status,a.imid,a.score,a.currScore,MAX(a.opDate)
        opDate ,IFNULL (a.sort,2) sort FROM (
        SELECT a.*,IFNULL(b.score,0)
        score,b.opDate,b.status sort FROM (
        SELECT
        a.*,b.imid FROM (
        SELECT a.* ,
        REPLACE(IFNULL(b.is_valid,1),2,1)
        STATUS
        FROM (
        SELECT
        b.id,b.userName,b.mobilePhone
        phone,b.gender,b.headImageUrl,b.shortNumber,b.newHouseId,b.newHouseName,b.currScore,b.regTime
        FROM hmc_new_house a LEFT
        JOIN
        hmc_consultant b ON a.id = b.newHouseId
        WHERE b.status = 2 AND a.newHouseId = '169331' AND a.city = 'nj'
        AND
        (UNIX_TIMESTAMP(NOW()) BETWEEN UNIX_TIMESTAMP(a.beginTime) AND
        UNIX_TIMESTAMP( a.endTime ))
        ) a
        LEFT JOIN hmc_auth_token b ON a.id =
        b.uid ) a LEFT
        JOIN im_domain_user b ON a.phone =
        b.phone ) a LEFT JOIN
        hmc_compete_bidding b ON a.id = b.userId
        ) a LEFT JOIN (
        SELECT b.* FROM
        hmc_new_house a INNER JOIN
        hmc_compete_bidding_simple b ON
        a.id =
        b.newHouseId
        WHERE a.newHouseId
        ='169331' AND a.city = 'nj' AND
        b.opDate < '2015-12-23') b ON a.id
        =
        b.createUserId GROUP BY phone ORDER BY
        STATUS ,sort,opDate DESC,score DESC ,currScore DESC ,regTime;

相關文章