比较两个表不同行,表的记录操作

日期:2019-10-03编辑作者:数据大全

#mysql中 对于查询结果只显示n条连续行的问题#

在领扣上碰到的一个题目:求满足条件的连续3行结果的显示

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people;
Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).
For example, the table stadium:
+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 1    | 2017-01-01 | 10        |
| 2    | 2017-01-02 | 109       |
| 3    | 2017-01-03 | 150       |
| 4    | 2017-01-04 | 99        |
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

For the sample data above, the output is:
+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+

1.首先先进行结果集的查询

select id,date,people from stadium where people>=100;

2.给查询的结果集增加一个自增列

SELECT @newid:=@newid+1 AS newid,test.* 
FROM(SELECT @newid:=0)r, test WHERE people>100

3.自增列和id的差值 相同即连续

SELECT @newid:=@newid+1 AS newid,test.* ,@cha:=id-@newid AS cha 
FROM(SELECT @newid:=0)r, test WHERE people>100

4.将相同的差值 放在同一张表中,并取出连续数量大于3的

select if(count(id)>=3,count_concat(id),null)e from(
SELECT @newid:=@newid+1 AS newid,test.* ,@cha:=id-@newid AS cha 
FROM(SELECT @newid:=0)r, test WHERE people>100)
as d group by cha

5.将上步得到的表和主表 取得所需要的

SELECT id,DATE,people FROM test,
(SELECT IF (COUNT(id)>3,GROUP_CONCAT(id),NULL)e 
FROM (SELECT @newid:=@newid+1 AS newid,test.* ,@cha:=id-@newid AS cha 
FROM(SELECT @newid:=0)r, test WHERE people>100)AS d   GROUP BY cha ) AS f 
WHERE f.e IS NOT NULL AND FIND_IN_SET(id,f.e);

听说还可以用存储过程来完成,不过我没尝试,稍后尝试

以上

 

怎样查询两个表中同一字段的不同数据值

很多时候我们需要重置某个表的自增列,让自增列重新从1开始记数。最蠢的方法当然是把该表删掉再重新建表了。其实,还有其它的方法可以重置自增列的值:

INSERT INTO bookcategory(category_id,category,parent_id) VALUES(1,'计算机',0);//指定插入的顺序


INSERT INTO bookcategory VALUES(1,'计算机',0);//按照默认的插入


INSERT INTO bookcategory(category_id,category,parent_id) VALUES(1,'计算机',0),(2,'xxx',3)(3,'xxxxx',4);//同时插入多条数据


INSERT INTO bookcategory SELECT * FROM test WHERE id>5//将查询结果插入表中


CREATE TABLE TEXT(

    category_id INT PRIMARY KEY AUTO_INCREMENT,

    parent_id INT NOT NULL

)AUTO_INCREMENT=5;    //auto_increment让这一列自动设置编号,默认初始值为,最后为设置初始值为5



ALTER TABLE bookcategory MODIFY category_id INT AUTO_INCREMENT=X//为已有表添加自增列



ALTER TABLE bookcategory MODIFY category_id INT;//去掉自增列


//有外键关系的话添加自增,要先去掉外键关系。


ALTER TABLE bookinfo DROP FOREIGN KEY fk_bcid//去掉外键


ALTER TABLE bookinfo ADD CONSTRAINT fk_bcid FOREIGN KEY(bookcdategory_id) REFERENCES bookcategory(category_id);//添加外键


SELECT price FROM bookinfo WHERE book_id=201501;//查询book_id为201501书的价格


SELECT * FROM readerinfo//查看读者信息表


SELECT xxx,xxx,xxx FROM readerinfo//查询指定列


SELECT xxx,xxx,xxx FROM readerinfo WHERE press='机械工业'//设置外加条件的查询


SELECT DISTINCT press FROM bookinfo;//显示不重复的查询


SELECT * FROM readerinfo WHERE age IS NULL;//查询为空的列


UPDATE readerinfo SET balance =balance -18.9*0.05 WHERE card_id='xxxxxxxx'//更新readerinfo这个表中的card_id为xxx的balance


DELETE FROM readerinfo WHERE card_id='201531513133'//单表数据记录的删除,不加where则为全部删除


TRUNCATE TABLE readerinfo;//删除原表再创建空表


SELECT COUNT(*) FROM readerinfo WHERE sex='男'//对查询结果的分组


SELECT sex FROM readerinfo GROUP BY sex;//查询有多少种性别


SELECT sex COUNT(*) FROM readerinfo GROUP BY sex;//统计每种性别的人数


SELECT sex FROM readerinfo GROUP BY sex WHERE COUNT(sex)>3;//也可以加限制条件。统计每种性别的人数


SELECT     * FROM bookinfo ORDER BY price//对查询结果排序,默认为升序


SELECT     * FROM bookinfo ORDER BY price,store;//price相同的,按照库存排序


SELECT * FROM bookinfo ORDER BY price ASC,store DESC;//asc升序,desc降序


SELECT * FROM bookinfo LIMIT 3;//limit限制查询数量,偏移量为0开始,显示前三行


SELECT * FROM bookinfo LIMIT 2,2;//显示第二条语句的后两个语句


SELECT store,COUNT(*) FROM bookinfo GROUP BY store ORDER BY store DESC LIMIT 4;//统计库存个数,并降序排序,并查看前四条语句

例如:

方法一:使用TRUNCATE TABLE语句: TRUNCATE TABLE删除表中的所有行,而不记录单个行删除操作,同时重置自增列。TRUNCATE TABLE 在功能上与没有WHERE子句的DELETE语句相同;但是,TRUNCATE TABLE 速度更快,使用的系统资源和事务日志资源更少。

 

A表中的字段a有40000条数据
B表中的字段a有60000条数据,其中的40000条数据跟A表是一样的
怎样能把那不一样的20000条数据查询出来啊?

方法二:使用DBCC CHECKIDENT语句: DBCC CHECKIDENT在 SQL Server 2008 R2 中检查指定表的当前标识值,如有必要,则更改标识值。还可以使用 DBCC CHECKIDENT 为标识列手动设置新的当前标识值。

 

 

语法:

INSERT INTO bookcategory(category_id,category,parent_id) VALUES(1,'计算机',0);//指定插入的顺序
INSERT INTO bookcategory VALUES(1,'计算机',0);//按照默认的插入
INSERT INTO bookcategory(category_id,category,parent_id) VALUES(1,'计算机',0),(2,'xxx',3)(3,'xxxxx',4);//同时插入多条数据
INSERT INTO bookcategory SELECT * FROM test WHERE id>5//将查询结果插入表中
CREATE TABLE TEXT(
category_id INT PRIMARY KEY AUTO_INCREMENT,
parent_id INT NOT NULL
)AUTO_INCREMENT=5; //auto_increment让这一列自动设置编号,默认初始值为,最后为设置初始值为5
ALTER TABLE bookcategory MODIFY category_id INT AUTO_INCREMENT=X//为已有表添加自增列
ALTER TABLE bookcategory MODIFY category_id INT;//去掉自增列
//有外键关系的话添加自增,要先去掉外键关系。
ALTER TABLE bookinfo DROP FOREIGN KEY fk_bcid//去掉外键
ALTER TABLE bookinfo ADD CONSTRAINT fk_bcid FOREIGN KEY(bookcdategory_id) REFERENCES bookcategory(category_id);//添加外键
SELECT price FROM bookinfo WHERE book_id=201501;//查询book_id为201501书的价格
SELECT * FROM readerinfo//查看读者信息表
SELECT xxx,xxx,xxx FROM readerinfo//查询指定列
SELECT xxx,xxx,xxx FROM readerinfo WHERE press='机械工业'//设置外加条件的查询
SELECT DISTINCT press FROM bookinfo;//显示不重复的查询
SELECT * FROM readerinfo WHERE age IS NULL;//查询为空的列
UPDATE readerinfo SET balance =balance -18.9*0.05 WHERE card_id='xxxxxxxx'//更新readerinfo这个表中的card_id为xxx的balance
DELETE FROM readerinfo WHERE card_id='201531513133'//单表数据记录的删除,不加where则为全部删除
TRUNCATE TABLE readerinfo;//删除原表再创建空表
SELECT COUNT(*) FROM readerinfo WHERE sex='男'//对查询结果的分组
SELECT sex FROM readerinfo GROUP BY sex;//查询有多少种性别
SELECT sex COUNT(*) FROM readerinfo GROUP BY sex;//统计每种性别的人数
SELECT sex FROM readerinfo GROUP BY sex WHERE COUNT(sex)>3;//也可以加限制条件。统计每种性别的人数
SELECT * FROM bookinfo ORDER BY price//对查询结果排序,默认为升序
SELECT * FROM bookinfo ORDER BY price,store;//price相同的,按照库存排序
SELECT * FROM bookinfo ORDER BY price ASC,store DESC;//asc升序,desc降序
SELECT * FROM bookinfo LIMIT 3;//limit限制查询数量,偏移量为0开始,显示前三行
SELECT * FROM bookinfo LIMIT 2,2;//显示第二条语句的后两个语句
SELECT store,COUNT(*) FROM bookinfo GROUP BY store ORDER BY store DESC LIMIT 4;//统计库存个数,并降序排序,并查看前四条语句

--建表table1,table2:   

DBCC CHECKIDENT (   table_name   [, { NORESEED | { RESEED [,new_reseed_value ] } } ]  )  [ WITH NO_INFOMSGS ]

1
2
3
4
5
6
7
8
9
create   table   table1(id   int,name   varchar(10));   
create   table   table2(id   int,score   int);   
insert   into   table1   select   '1','lee';
insert   into   table1   select   '2','zhang';
insert   into   table1   select   '3','steve';
insert   into   table1   select   '4','wang';   
insert   into   table2   select   '1','90';   
insert   into   table2   select   '2','100';   
insert   into   table2   select   '3','70';

参数: table_name:是要对其当前标识值进行检查的表名。指定的表必须包含标识列。表名必须符合标识符规则。 NORESEED:指定不应更改当前标识值。 RESEED:指定应该更改当前标识值。 new_reseed_value:用作标识列的当前值的新值。 WITH NO_INFOMSGS:取消显示所有信息性消息。

 

示例:我们要重置表t1的当前标识值为0,sql如下:

如表
-------------------------------------------------   
table1    
-------------------------------------------------   
id name  
1 lee
2 zhang

dbcc checkident('t1',reseed,0)

3 steve
4 wang

我们要查询表t1的当前标识值,sql如下:

-------------------------------------------------  

dbcc checkident('t1',noreseed)

table2

本文来源于:

------------------------------------------------- 

id score
1 90
2 100

3 70

------------------------------------------------- 

 

(1)左向外联接的结果集包括   left   outer   子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值(null)。  

(2)sql语句  

1
select from table1 t1 left join table2 t2 on t1.id = t2.id

 

-------------结果-------------   
id name id score   
------------------------------   
1 lee 1 90   
2 zhang 2 100  

3 steve 3 70
4 wang null null   
------------------------------  

注释:包含table1的所有子句,根据指定条件返回table2相应的字段,不符合的以null显示

(3)那么获取差值

1
select from table1 t1 left join table2 t2 on t1.id = t2.id WHERE t2.id is null

 

-------------结果-------------   
id name id score  

4 wang null null   
------------------------------  

 

下面是工作中实际遇到的情况:

##过滤出0销售人员(即没有销售记录的员工信息列表)。

#销售人员(用户角色中间表)

1
select userid from bbscs_role_user where roleid = 'sales'

 

 # ---> 11条记录

 

#统计表(用户销售记录表)

1
select refid from bbscs_sales_income_stat where type = 4 and month '2012-02' and amount != 0

 

 # ---> 4条记录

要求为:另外7个销售人员的记录列出来为目的。

 

##########这个是SQL语句模型 BEGIN##########

1
select from b t2 left join a t1 on t1.a1 = t2.b1 WHERE t1.a1 is null

 

#########这个是SQL语句模型 END############

说明:左表是数据多的那个表(基准表如b表)。left join查询。where条件是右边的那个表(a表)某个字段(a1)为Null作为(判断字段)

 

##将SQL返回结果作为临时表来查询

1
2
3
select from (select userid from bbscs_role_user where roleid = 'sales') t2 left 
join (select refid from bbscs_sales_income_stat where type = 4 and month '2012-02' 
and amount != 0) t1 on t2.userid = t1.refid WHERE t1.refid is null

 

 # --->7条记录

 

澳门金莎娱乐手机版, 

测试一:

##SQL语句,mysql 查询两个表中不同的值(主要是差值)  这个语句查询还是存在问题。

1
2
select t1.Userid from bbscs_role_user t1 left join bbscs_sales_income_stat t2 on t1.userid = t2.refid 
and t1.roleid = 'sales' and t2.type = 4 and t2.month '2012-02' and t2.amount != 0 where t2.id is null;

 ##表与表,条件与条件独立出来。

 # --->18条记录

 

 

测试二:

1
2
select t1.Userid from bbscs_role_user t1 left join bbscs_sales_income_stat t2 on t1.userid = t2.refid 
and t1.roleid = 'sales' and t2.type = 4 and t2.month '2012-02' and t2.amount != 0 and t2.id is null

##where or and 区别

 # --->22条记录

 

 

###更为强大的临时表查询功能,将以上查询结果作为一个整体放入。

##跟用户部门中间表关联,按部门id排序显示。

 

1
2
3
select t4.userid fromselect from (select userid from bbscs_role_user where roleid = 'sales') t2 left join 
(select refid from bbscs_sales_income_stat where type = 4 and month '2012-02' and amount != 0) t1 on 
t2.userid = t1.refid WHERE t1.refid is null ) t3, bbscs_org_user t4 where t3.userid = t4.userid order by orgId

本文由澳门金莎娱乐网站发布于数据大全,转载请注明出处:比较两个表不同行,表的记录操作

关键词:

Mysql数据库的接口MySQLdb澳门金莎娱乐手机版,课

2:MySQL的装置与安排 importMySQLdb Python连接mysql数据库的正确性姿势,pythonmysql数据库 Python 数据库接口协助相当多的数...

详细>>

MSSQL数据库表索引碎片整理优化性能,索引阐述系

-- 创建聚集索引create table [dbo].[pub_stocktest] add constraint [pk_pub_stocktest] primary key clustered ([sid] asc)with (pad_index = off, stati...

详细>>

数据类型,Server数据类型char

1.电话字段设置24个Byte竟然不够,好吧设置为50的长度。 alter table 表名 alter column 字段名 新类型名(长度)举例:alter ...

详细>>

SqlServer递归查询

1 DECLARE @ParentId NVARCHAR(36); 2 SET @ParentId = '078499bf-dedd-b293-4653-7bf6a2e54cbb'; 3 4 WITH [Temp] AS 5 ( 6 SELECT [A].* FROM [dbo].[Contacts]...

详细>>