mysql中sql的混乱写法一二三

最新推荐文章于 2022-06-26 22:53:40 发布

弘恩

最新推荐文章于 2022-06-26 22:53:40 发布

阅读量838

点赞数

CC 4.0 BY-SA版权

文章标签： mysql rowNumber

本文链接：https://blog.youkuaiyun.com/c_enhui/article/details/80061065

本文介绍了一种使用SQL语句来创建临时表并填充数据的方法。通过联接多个表来筛选符合特定条件的服务部署记录，并为每个记录分配唯一标识符及排名。此过程涉及到了复杂的SQL操作，如使用变量来简化重复字段的处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

use dbo; -- 感谢一下我背后的王同学
DROP TABLE IF EXISTS tmp_ac_t_paas_service_deploy;
CREATE TABLE tmp_ac_t_paas_service_deploy
SELECT a.*, @rownum := @rownum + 1 `id`,
  if(@Product = a.`Product` and @service_name = a.service_name, @rank := @rank + 1, @rank := 1) AS `rank`,
  @Product := a.`Product`,
  @service_name := a.service_name
  FROM (SELECT DISTINCT
    a.name,
    a.status,
    a.git_repo,
    b.`Product`,
    c.build_no,
    c.service_name,
    profile,
    c.ctime,
    c.mtime
  FROM t_paas_service `a`
    JOIN Git_Product_Service_Mapping `b` ON replace(REPLACE(a.git_repo, 'http://git.jizibaodian.com/',
                                                            'git@git.jizibaodian.com:'), 'https://git.jizibaodian.com/',
                                                    'git@git.jizibaodian.com:')
                                            = b.Git_Address_Revised
    JOIN t_paas_service_deploy `c` ON a.id = c.service_id

  WHERE a.git_repo <> '' AND c.status <> -1
        #AND b.`Product` = 'jizibao'
        AND profile = 'prod'
        AND c.ctime >= date_format('2017-12-25', '%Y-%m-%d')
  ORDER BY b.`Product`, c.service_name, c.ctime) a,

(select @rownum := 0, @Product := null, @service_name := null, @rank := 0) b;

ALTER TABLE tmp_ac_t_paas_service_deploy ADD PRIMARY KEY (id);
ALTER TABLE tmp_ac_t_paas_service_deploy DROP `@Product := a.`Product``;
ALTER TABLE tmp_ac_t_paas_service_deploy DROP `@service_name := a.service_name`;