22,207
社区成员
发帖
与我相关
我的任务
分享
SELECT UserID, SUM(Amount) AS Amount,
(SELECT TOP (1) DisplayName
FROM dbo.RangeAchievements AS b
WHERE (UserID = a.UserID)) AS DisplayName,
(SELECT TOP (1) DeptName
FROM dbo.RangeAchievements AS b
WHERE (UserID = a.UserID)) AS DeptName,
(SELECT TOP (1) HeadPhoto
FROM dbo.RangeAchievements AS b
WHERE (UserID = a.UserID)) AS HeadPhoto,
(SELECT TOP (1) DeptID
FROM dbo.RangeAchievements AS b
WHERE (UserID = a.UserID)) AS DeptID
FROM dbo.RangeAchievements AS a
where CreateTime>
(SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
and
CreateTime
<
(SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) )
GROUP BY UserID
-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
DROP FUNCTION [fn_test]
GO
CREATE FUNCTION [fn_test](@参数 varchar(20))
RETURNS TABLE
AS
RETURN
SELECT * FROM [tb] WHERE name like '%'+@参数+'%'
GO
--调用:
SELECT * FROM [fn_test]('县')
/*
id name fid
----------- ---------- -----------
110228 密云县 110200
110229 延庆县 110200
(2 行受影响)
*/
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-10 10:25:02
-- 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)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([id] [int],[name] [nvarchar](10),[fid] [int])
INSERT INTO [tb]
SELECT '110101','东城区','110100' UNION ALL
SELECT '110102','西城区','110100' UNION ALL
SELECT '110103','崇文区','110100' UNION ALL
SELECT '110104','宣武区','110100' UNION ALL
SELECT '110105','朝阳区','110100' UNION ALL
SELECT '110106','丰台区','110100' UNION ALL
SELECT '110107','石景山区','110100' UNION ALL
SELECT '110108','海淀区','110100' UNION ALL
SELECT '110109','门头沟区','110100' UNION ALL
SELECT '110111','房山区','110100' UNION ALL
SELECT '110112','通州区','110100' UNION ALL
SELECT '110113','顺义区','110100' UNION ALL
SELECT '110114','昌平区','110100' UNION ALL
SELECT '110115','大兴区','110100' UNION ALL
SELECT '110116','怀柔区','110100' UNION ALL
SELECT '110117','平谷区','110100' UNION ALL
SELECT '110228','密云县','110200' UNION ALL
SELECT '110229','延庆县','110200'
-->SQL查询如下:
IF OBJECT_ID('[fn_test]') IS NOT NULL
DROP FUNCTION [fn_test]
GO
CREATE FUNCTION [fn_test](@参数 varchar(20))
RETURNS TABLE
AS
RETURN
SELECT * FROM [tb] WHERE name=@参数
GO
--调用:
SELECT * FROM [fn_test]('西城区')
/*
id name fid
----------- ---------- -----------
110102 西城区 110100
(1 行受影响)
*/
参考