如何将表连接到表AS temprery表 [英] how to join table to table AS temprery table
问题描述
我从下面的命令得到表格
SELECT * FROM [MATRIMONIAL]。[dbo] .User_profile1
DECLARE @ time_duration INT
SET @time_duration =( SELECT Time_duration FROM [MATRIMONIAL]。[dbo] .User_profile1 WHERE Pro_id = ' 002')
DECLARE @ d1 DATETIME
SET @ d1 = GETDATE();
DECLARE @ d2 DATETIME
SET @ d2 =( SELECT Start_date FROM dbo.User_profile1 WHERE Pro_id = ' 002')
SELECT dbo.Left_membership( @ d2 , @ d1 , @ time_duration )
Left_membership(@ d2,@ d1,@ time_duration)是函数
并希望将表绑定在单个grid-view我该怎么做?
SELECT *
FROM [MATRIMONIAL]。[dbo] .User_pro file1
LEFT JOIN dbo.Left_membership( @ d2 , @ d1 , @ time_duration ) as tbl on tbl。[field] = User_profile1。[field]
你不必复杂这样的事情。让我举一个例子:
- create table USER_PROFILE(
- ID int,
- 名称varchar(50),
- START datetime,
- DURATION int
- )
< span class =code-comment> - 插入USER_PROFILE(ID,NAME,START,DURATION)
- 值
- (1,'user1',DATEADD(m,-2,getdate()),90),
- (2,'user2' ,DATEADD(m,-3,GETDATE()),60)
选择 ID,NAME, DURATION-DATEDIFF(d,START,GETDATE()) as SUBS_LEFT,
( case < span class =code-keyword>当 DATEADD(d,DURATION,START)> GETDATE()然后 0 其他 1 end )IS_ENDED
来自 USER_PROFILE
然后在此处阅读: http://msdn.microsoft.com/en-us/library/314t4see.aspx [ ^ ]
和此处: http://msdn.microsoft.com/en -us /库/ 33w255ac%28V = v s.110%29.aspx [ ^ ]
1-创建项目
2-添加数据集
3-使用给定查询创建数据表
4-在表单上放置gridview。
5-将gridview的数据源设置为数据表数据集
所有代码都由数据集设计者和表单设计者自动生成。你不需要写任何东西。如果您愿意,可以根据需要稍后更改所有代码。这就是全部。
[已添加]
选择 ID,NAME,DURATION-DATEDIFF(d,START,GETDATE()) SUBS_LEFT,
( case 何时 DATEADD(d,DURATION,START)> GETDATE()
convert ( varchar ,DURATION-DATEDIFF(d, START,GETDATE()))
else ' 已过期' 结束)IS_ENDED
来自 USER_PROFILE
i get table from the below command
SELECT * FROM [MATRIMONIAL].[dbo].User_profile1
DECLARE @time_duration INT
SET @time_duration=(SELECT Time_duration FROM [MATRIMONIAL].[dbo].User_profile1 WHERE Pro_id='002')
DECLARE @d1 DATETIME
SET @d1= GETDATE();
DECLARE @d2 DATETIME
SET @d2 = (SELECT Start_date FROM dbo.User_profile1 WHERE Pro_id='002' )
SELECT dbo.Left_membership(@d2,@d1,@time_duration)
Left_membership(@d2,@d1,@time_duration) is function
and want to bind the table in single grid-view how can i do this?
SELECT * FROM [MATRIMONIAL].[dbo].User_profile1 LEFT JOIN dbo.Left_membership(@d2,@d1,@time_duration) as tbl on tbl.[field] = User_profile1.[field]
you don't have to complicate things like that. let me give an example:
--create table USER_PROFILE ( -- ID int, -- NAME varchar(50), -- START datetime, -- DURATION int --) --insert into USER_PROFILE(ID, NAME, START, DURATION) --values --(1, 'user1', DATEADD(m, -2, getdate()), 90), --(2, 'user2', DATEADD(m, -3, GETDATE()), 60) select ID, NAME, DURATION-DATEDIFF(d, START, GETDATE())as SUBS_LEFT, (case when DATEADD(d, DURATION, START)>GETDATE() then 0 else 1 end) IS_ENDED from USER_PROFILE
then read here: http://msdn.microsoft.com/en-us/library/314t4see.aspx[^]
and here: http://msdn.microsoft.com/en-us/library/33w255ac%28v=vs.110%29.aspx[^]
1- create a project
2- add a dataset
3- create your datatable with the given query
4- place a gridview on your form.
5- set the datasource of the gridview as the datatable of the dataset
all code is automatically generated by both dataset designer and form designer. you don't need to write anything. if you like, you can change all that code later according to your needs. that is all.
[added]
select ID, NAME, DURATION-DATEDIFF(d, START, GETDATE())as SUBS_LEFT, (case when DATEADD(d, DURATION, START)>GETDATE() then convert(varchar, DURATION-DATEDIFF(d, START, GETDATE())) else 'Expired' end) IS_ENDED from USER_PROFILE
这篇关于如何将表连接到表AS temprery表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!