usertable username
password
sex
birthday
address
Interest
modtimeusername password sex birthday address interest modtime
001 123456 M 19880808 广东省 上网 2010-01-08 19:12
002 112233 W 19900121 江西省 音乐 2010-01-27 12:33
003 aa1111 M 19870823 浙江省 书籍;运动 2010-01-06 08:29
messagetable username
Content
posttimeusername content posttime
001 test11111 2010-01-02 19:39
001 test12222 2010-01-02 19:40
002 test21111 2010-01-02 19:42
002 test22222 2010-01-02 19:42
002 test23333 2010-01-02 19:43
003 test31111 2010-01-02 19:44
要求搜索结果:username contents modtime
003 1 2010-01-06 08:29
001 2 2010-01-08 19:12
002 3 2010-01-27 12:33
根据表一的modtime排序,username和contents是表二的值 modtime是表一的值
用搜索语句 select username,Count(content) AS contents from messagetable GROUP BY username
可以实现前两项的查询,请问怎么添加最后一项进入查询结果呢?
password
sex
birthday
address
Interest
modtimeusername password sex birthday address interest modtime
001 123456 M 19880808 广东省 上网 2010-01-08 19:12
002 112233 W 19900121 江西省 音乐 2010-01-27 12:33
003 aa1111 M 19870823 浙江省 书籍;运动 2010-01-06 08:29
messagetable username
Content
posttimeusername content posttime
001 test11111 2010-01-02 19:39
001 test12222 2010-01-02 19:40
002 test21111 2010-01-02 19:42
002 test22222 2010-01-02 19:42
002 test23333 2010-01-02 19:43
003 test31111 2010-01-02 19:44
要求搜索结果:username contents modtime
003 1 2010-01-06 08:29
001 2 2010-01-08 19:12
002 3 2010-01-27 12:33
根据表一的modtime排序,username和contents是表二的值 modtime是表一的值
用搜索语句 select username,Count(content) AS contents from messagetable GROUP BY username
可以实现前两项的查询,请问怎么添加最后一项进入查询结果呢?
解决方案 »
- SQL SERVER 有增量备份吗?求方法和脚本,为啥我只能完全、差异、事务日志这3种备份!
- 关于日期排序
- SQL链接服务器中的表和视图是哪里来的?
- 以下过程获取一个查询字符串,字符串却不完整,求解
- 数据库被加马了
- 怎么样才能导出非固定角色执行语句权限的脚本(再发贴求解答)
- 有没有哪位大神有一现成的数据库,数据最好成千上万的
- 神仙们,救命了!错误提示:Fatal error: Call to undefined function mssql_connect() ,请问,如何配置才能用php连接上sqlserver2000?
- 求一个简单sql两个表查询
- sql server 2008 R2 的系统数据库被破坏了,可以修复吗?
- SQL 2005里用存储过程发送邮件
- SQL存储过程INSERT语句是否可以同时使用参数值和SELECT语句传递值
from messagetable M
inner join usertable U on M.username = U.username
GROUP BY M.username,U.modtime
--> 测试数据:[usertable]
if object_id('[usertable]') is not null drop table [usertable]
create table [usertable]([username] varchar(3),[password] varchar(6),[sex] varchar(1),[birthday] datetime,[address] varchar(6),[interest] varchar(9),[modtime] datetime)
insert [usertable]
select '001','123456','M','19880808','广东省','上网','2010-01-08 19:12' union all
select '002','112233','W','19900121','江西省','音乐','2010-01-27 12:33' union all
select '003','aa1111','M','19870823','浙江省','书籍;运动','2010-01-06 08:29'
--> 测试数据:[messagetable]
if object_id('[messagetable]') is not null drop table [messagetable]
create table [messagetable]([username] varchar(3),[content] varchar(9),[posttime] datetime)
insert [messagetable]
select '001','test11111','2010-01-02 19:39' union all
select '001','test12222','2010-01-02 19:40' union all
select '002','test21111','2010-01-02 19:42' union all
select '002','test22222','2010-01-02 19:42' union all
select '002','test23333','2010-01-02 19:43' union all
select '003','test31111','2010-01-02 19:44'select * from [usertable]
select * from [messagetable]
select M.username,COUNT(M.content) AS contents ,U.modtime
from messagetable M
inner join usertable U on M.username = U.username
GROUP BY M.username,U.modtime
/*
username contents modtime
-------- ----------- -----------------------
001 2 2010-01-08 19:12:00.000
002 3 2010-01-27 12:33:00.000
003 1 2010-01-06 08:29:00.000(3 行受影响)*/
-- Author : htl258(Tony)
-- Date : 2010-04-21 08:04:57
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
--------------------------------------------------------------------------
--> 生成测试数据表:userIF NOT OBJECT_ID('[user]') IS NULL
DROP TABLE [user]
GO
CREATE TABLE [user]([username] NVARCHAR(10),[password] NVARCHAR(10),[sex] NVARCHAR(10),[birthday] DATETIME,[address] NVARCHAR(10),[interest] NVARCHAR(10),[modtime] DATETIME)
INSERT [user]
SELECT '001','123456','M','19880808',N'广东省',N'上网',N'2010-01-08 19:12' UNION ALL
SELECT '002','112233','W','19900121',N'江西省',N'音乐',N'2010-01-27 12:33' UNION ALL
SELECT '003','aa1111','M','19870823',N'浙江省',N'书籍;运动',N'2010-01-06 08:29'
GO
--SELECT * FROM [tb]--> 生成测试数据表:messageIF NOT OBJECT_ID('[message]') IS NULL
DROP TABLE [message]
GO
CREATE TABLE [message]([username] NVARCHAR(10),[content] NVARCHAR(10),[posttime] DATETIME)
INSERT [message]
SELECT '001','test11111',N'2010-01-02 19:39' UNION ALL
SELECT '001','test12222',N'2010-01-02 19:40' UNION ALL
SELECT '002','test21111',N'2010-01-02 19:42' UNION ALL
SELECT '002','test22222',N'2010-01-02 19:42' UNION ALL
SELECT '002','test23333',N'2010-01-02 19:43' UNION ALL
SELECT '003','test31111',N'2010-01-02 19:44'
GO
--SELECT * FROM [message]-->SQL查询如下:
select username,content=(select COUNT(1) from [message] where username = [user].username),modtime from [user]
/*
username content modtime
---------- ----------- -----------------------
001 2 2010-01-08 19:12:00.000
002 3 2010-01-27 12:33:00.000
003 1 2010-01-06 08:29:00.000(3 行受影响)
*/
select username,content=(select COUNT(1) from [message] where username = [user].username),convert(varchar(16),modtime,20) modtime from [user] order by 2
/*
username content modtime
---------- ----------- ----------------
003 1 2010-01-06 08:29
001 2 2010-01-08 19:12
002 3 2010-01-27 12:33(3 行受影响)
*/排序后
from usertable a,messagetable b
where a.username=b.username
GROUP BY a.username
order by max(modtime)
INSERT [user]
SELECT '001','123456','M','19880808',N'广东省',N'上网',N'2010-01-08 19:12' UNION ALL
SELECT '002','112233','W','19900121',N'江西省',N'音乐',N'2010-01-27 12:33' UNION ALL
SELECT '003','aa1111','M','19870823',N'浙江省',N'书籍;运动',N'2010-01-06 08:29'
GOCREATE TABLE [message]([username] NVARCHAR(10),[content] NVARCHAR(10),[posttime] DATETIME)
INSERT [message]
SELECT '001','test11111',N'2010-01-02 19:39' UNION ALL
SELECT '001','test12222',N'2010-01-02 19:40' UNION ALL
SELECT '002','test21111',N'2010-01-02 19:42' UNION ALL
SELECT '002','test22222',N'2010-01-02 19:42' UNION ALL
SELECT '002','test23333',N'2010-01-02 19:43' UNION ALL
SELECT '003','test31111',N'2010-01-02 19:44'
GO--1
select m.username , count(1) contents , m.modtime from [user] m, message n where m.username = n.username group by m.username,m.modtime order by contents--2
select m.username , contents = (select count(1) from message n where m.username = n.username) , m.modtime from [user] m order by contentsdrop table message, [user]/*
username contents modtime
---------- ----------- ------------------------------------------------------
003 1 2010-01-06 08:29:00.000
001 2 2010-01-08 19:12:00.000
002 3 2010-01-27 12:33:00.000(所影响的行数为 3 行)username contents modtime
---------- ----------- ------------------------------------------------------
003 1 2010-01-06 08:29:00.000
001 2 2010-01-08 19:12:00.000
002 3 2010-01-27 12:33:00.000(所影响的行数为 3 行)*/
USE myDB
/*创建usertable测试表*/
IF OBJECT_ID('usertable')IS NOT NULL
DROP TABLE usertable
GO
CREATE TABLE usertable
(
username VARCHAR(20) NOT NULL,
password VARCHAR(20) NOT NULL,
sex CHAR(2) NOT NULL,
birthday VARCHAR(20) NOT NULL,
address VARCHAR(20) NOT NULL,
Interest VARCHAR(20) NOT NULL,
modtime VARCHAR(20) NOT NULL
)
GO
SELECT * FROM usertable
/*对表usertable中插入测试数据*/
INSERT usertable
SELECT '001','123456','M','19880808','广东省','上网','2010-01-08 19:12' UNION ALL
SELECT '002','112233','M','19900121','浙江省','音乐','2010-01-21 21:02' UNION ALL
SELECT '003','aa1111','M','19890122','江西省','跳舞','2010-01-08 23:45'
GO
/*创建messagetable测试表*/
IF OBJECT_ID('messagetable')IS NOT NULL
DROP TABLE messagetable
GO
CREATE TABLE messagetable
(
username VARCHAR(20) NOT NULL,
Content VARCHAR(20) NOT NULL,
posttime VARCHAR(20) NOT NULL
)
GO
/*对表messagetable中插入测试数据*/
INSERT messagetable
SELECT '001','test11111','2010-01-02 19:39' UNION ALL
SELECT '001','test12222','2010-01-02 19:39' UNION ALL
SELECT '002','test21111','2010-01-02 19:42' UNION ALL
SELECT '002','test22222','2010-01-02 19:42' UNION ALL
SELECT '002','test23333','2010-01-02 19:43' UNION ALL
SELECT '003','test31111','2010-01-02 19:44'
GO
SELECT * FROM messagetable
DELETE messagetable WHERE username>=1
select username,content=(select COUNT(1) from messagetable where username = usertable.username),SUBSTRING(modtime,1,20) modtime from usertable order by 2
/*
--测试结果
username content modtime
003 1 2010-01-08 23:45
001 2 2010-01-08 19:12
002 3 2010-01-21 21:02
*/