Try this:
DECLARE @tbl Table (userID int , StartDT datetime, EndDT datetime)
INSERT INTO @tbl (userID, StartDT, EndDT)
Select 1, '12/02/2011', '01/16/2012'
UNION ALL
Select 1, '03/04/2012', '03/24/2012'
UNION ALL
Select 1, '04/05/2012', '04/26/2012'
UNION ALL
Select 1, '05/14/2012', '06/07/2012'
UNION ALL
Select 2, '03/05/2012', '03/30/2012'
UNION ALL
Select 2, '08/04/2012', '09/15/2012'
UNION ALL
Select 2, '04/01/2012', '04/30/2012'
UNION ALL
Select 3, ... 阅读全帖
借idtknow的思路，改进一下。比较不同行的日期，而不是同一行的日期。希望没有虫子
SELECT userID, StartDT, EndDT,
ROW_NUMBER() OVER (partition by userID order by StartDT) rk,
DATEDIFF(dd, StartDT, EndDT) diff
into #ttt
FROM #tbl
SELECT a.userID, a.rk, a.StartDT as A_Start, a.EndDT as A_End, b.StartDT as
B_Start, b.EndDT as B_End
into #shift
FROM #ttt a
LEFT JOIN #ttt b on a.userID = b.userID and a.rk + 1 = b.rk
order by a.userID, a.rk
select userID, min(A_Start)as StartDate, max(case when B_End IS null or
Cat= 'Y' then A_End else B_E... 阅读全帖
1. Deadlock describes a situation that two or more threads (processes) are
blocked forever, waiting for each other.
Causes: one thread needs to visit the resource that another thread is
possessing and vice versa.
Effects: If deadlock happens, the threads involved will hang there forever
in an undesired status. Deadlock should be avoided.
2.
public ArrayList getToyotas(ArrayList cars)
{
if(cars == null) return null;
I used sqlhelper class from Application block.
"ses" is my session variable class and ses.s is temporary session variable.
below is the code:
/*method validUserID is to verify id*/
Public Shared Function validUserID(ByVal userid As String) As Boolean
ses.s = (SqlHelper.ExecuteScalar(db.db_connection, CommandType.
StoredProcedure, sp.dptValidateUserID, New SqlParameter("@Userid", userid)))
db.CloseIfOpen()
If userid = ses.s Then
ses.UserId = userid
... 阅读全帖
DECLARE @UserID AS INT
DECLARE @UserName AS VARCHAR(50)
SET @UserID = 3
SELECT @UserName = UserName
FROM dbo.[User]
WHERE UserID = @UserID
SELECT gm.GroupID
, @UserID AS UserID
, MAX(g.GroupName) AS GroupName
, COUNT(1) AS cnt
, @UserName AS CreatorName
FROM dbo.GroupMember gm
INNER JOIN dbo.[Group] g
ON gm.GroupID = g.GroupID
WHERE gm.GroupID IN ( SELECT GroupID
FROM dbo.GroupMember
WHERE Use... 阅读全帖
把老祖宗解答放这里压阵，不精版理不容啊。
把同组的EndDate都设成同一个，再挑最小的StartDate。
--------------------------------------
select UserId,min(StartDate) as StartDate, EndDate
from
(select UserId,StartDate,
(select min(EndDate)
from work_time B
where A.UserId=B.UserId and A.StartDate<=B.StartDate
and not exists
(select *
from work_time C where B.UserId=C.UserId and B.StartDate
StartDate
and datediff(day,B.EndDate,C.StartDate)<30)
... 阅读全帖
Select distinct(friendid)
from users u
join friends f on u.userid = f.userid
where u.userid = xxxxxxx
and u.zipcode = xxxxxxx
话说TABLE users 的primary key是userid吧? 那么zipcode可以不用管：
Select distinct(friendid)
from users u
join friends f on u.userid = f.userid
where u.userid = xxxxxxx
1. check if table2, table3 has primary key userid.
if not, add.
2.
insert into tables1(userid, nickname, msn)
select a.userid, a.nickname, b.msn
from table2 a, table3 b
where a.userid = b.userid.
1. check if table1 has primary key userid.
if not, add.
Note: IF userid is not unique in table 2, table3, kick the person who design
these tables.
select distinct userid
from thisTable t1
where exists(select * from thisTable t2 where t2.userid = t1.userid and t2.
region = 'B' )
and exists(select * from thisTable t2 where t2.userid = t1.userid and t2.
region = 'C' )
and not exists(select * from thisTable t2 where t2.userid = t1.userid and
t2.region not in ('B','C') )
Web development职位，不是很大的公司，先发邮件来做题，第一次碰到。
刚刚做完，过会发我的sample答案上来
1. Describe a deadlock. What causes it? What are the effects?
2. Consider class Car with method getMake() -> String. Write a function to
retrieve all Toyotas from the following data structure:
ArrayList cars
3. List the bugs/problems in the following code snippets:
a. select * from claim where diagnosis_id in (739.1) and where patient_id in
(select patient_id from patient where name is ‘SMITH’)
b.with a as (select * from claim)
s... 阅读全帖
两个表,
users: userid, username, zipcode
friends: userid, friendid (also an userid), friendname
找出一个 user 的所有在某个 zipcode 的 friends id.
我想出来的答案：
select userid, zipcode from users A where A.userid in (select friendid from
friends B where B.userid = "****") and A.zipcode = "***";
大家觉得这个答案如何？
SELECT CASE
WHEN a.userid IS NULL
THEN b.userid
ELSE a.userid
END AS userid,
a.nickname,
b.msn
FROM table2 a FULL OUTER JOIN table3 b ON a.userid = b.userid
GroupMember Table结构请见图片1。
其中，GroupMemberID是PK。UserID是FK。
我想实现的功能。
UserID=3,作为一个输入的变量，然后列出该用户所在的所有Group,并且统计该Group里
相关的人数。
前半部分，也就是显示该用户所在的所有Group我已经解决，因为这个最容易。但是如
何显示出相关的人数，这个部分真的让我非常非常的抓狂啊。。。
我瞎写了一小段代码，请高手们指教。
SELECT COUNT(UserID) AS Counter
FROM GroupMembers
GROUP BY UserID
运行以上代码，可以显示出TABLE里所有不同的GROUP的人数。
但是如果我这样写。
SELECT COUNT(UserID) AS Counter
FROM GroupMembers
WHERE UserID=3
GROUP BY GroupID
结果就是错误的。。。。
毕竟不是专业DBA。。。。郁闷死我了。请高手们赐教，万分感谢。
Again, thank you very much for everyone who kindly helped me in this. I am
already learning a lot as a db design newbie....
Yes, I totally agree with you.
2 is a very interesting point.Sql Server supports varchar(8000), and for
larger data you have to use varchar(max), which may not be in row.
I am thinking about adding a hash column so that I can do easy comparison
and also index.
1. is also very interesting. I am debating if I should add a table to
capture the sub-directory information like ge... 阅读全帖
理解错了
select userid from table
where region = 'b' and userid in (
select userid from table&
#160;whereregion = 'c'
) and userid not in (
select userid from table&
#160;whereregion = 'a'
)
这段angularjs的代码：
var User = $resource('/user/:userId', {userId:'@id'});
var user = User.get({userId:123}, function() {
user.abc = true;
user.$save();
});
这个过程中，总共有2次呼唤http:
第一次，http get,call的 localhost/user/123
就是从服务器端拿到userid 123的user数据：
var user = User.get({userId:123}, function() {
第二次，http post,就是user.$save();但是，这个post的url竟然是
localhost/user/123，不是localhost/user,所以出错。按说，这个save用的url
localhost/user/123其实是update/put(update an existing record)用的url,不是
post(insert a new record)用的url.出错就是出在这儿。... 阅读全帖
【 以下文字转载自 Database 讨论区 】
发信人: yh1213 (yh), 信区: Database
标 题: An interview question: data store schema design
发信站: BBS 未名空间站 (Sat Mar 24 18:30:54 2012, 美东)
Design a backend store for storing metadata about files and directories
belonging to different users as part of an online storage service. The
logical data model is as follows:
--a user has zero or more files or directories
--a directory contains zero or more files or sub-directories
--a file or directory has a relative path that is up to 32K... 阅读全帖
On a server, I have a directory /path/to/my/userId
I have a web directory like /path/to/my/userId/public_html
I can access the index like http://web.domain.edu/~userId/index.html
if I want to install wordpress or something the like into
/path/to/my/userId/public_html/wordpress
is it possible to access the wordpress pages without type the folder
name wordpress in the url?
Any help will be appriciated!
It could be possible with regex replacement. I am not an expert, but
you could try something like the following (not tested):
RewriteEngine On
RewriteRule ^/~userId/*$ /~userId/wordpress/index.php?$1 [L,QSA]
so when people tries to access
/~userId/content
it would be forwarded to
/~userId/wordpress/index.php?content
.htaccess documentation is available online, so you can read more about
it.
nod
should be:
select userid_1
from
(select t1.userid as userid_1, t2.userid as userid_2 from t1 left join t2 on
t1.userid=t2.userid)
where userid_2 is null
Sorry I can not write Chinese here.
In MySQL, I have two databases: newdb and olddb
table1 in newdb, table2 and table3 in olddb
table1 has userid, nickname, msn
table2 has userid, nickname
table3 has userid msn
First I use NaviCat to import table2 into table1, very fast! 320,000 records
in 2 mins!
Then I tried to import table 3 into table1, now I have to check userid first
, then UPDATE table1. Very slow. 50,000 records in 12 hours
Anyway I can make it fast?
Thanks
Sorry to bother high hands again. Thanks for your help before!
Table a, articleid (pk), userid, datetime, textbody
Table b, userid (pk), nickname
I want one record, that's the latest article, with nickname, datetime and
textbody.
So I wrote this:
SELECT a.DateTime,b.Nickname,a.textbody
FROM a, b
WHERE a.userid=b.userid
ORDER BY datatime DESC
LIMIT 0, 1
But I feel this might be slow. Any way to make it fast?
I told my team leader we should write this as a stored procedure, but he
refused.
Design a backend store for storing metadata about files and directories
belonging to different users as part of an online storage service. The
logical data model is as follows:
--a user has zero or more files or directories
--a directory contains zero or more files or sub-directories
--a file or directory has a relative path that is up to 32KB in length
--a file has a size and last modified time
This store should handle the following online queries efficiently:
--lookups on single files or sub-d... 阅读全帖
是要这样吗?
With C As(
select description, t2.userid, program
from table1 as t1
left join table2 as t2
on t1.id = t2.id
left join table3 as t3
on t2.userid = t3.userid
)
select description, A, B, C
from C pivot(count(userid) for program in (A, B, C)) as P ;
----------------------------------------------------------
description A B C
---------------------------
0-2 1 0 1
2-5 0 0 0
5-10 0 0 1
more than 10 0 1 0
hash_map >
typedef hash_map >::iterator itertype;
hash_map
u only need to search userid once to find all user ids on a server. need to
be careful with update methods though. not sure whether this is what u want.
..
1. some basic c++ questions, such as emutable variable, virtual destructor
2. tree
3. hash table vs binary tree, pros and cons
4 . design a library system
5. code implements
has a log file, which include userid, timestamp (when click a link).
All clicks in one hour call one section,
ask find the userid, who has max click in one section.
after this, he asked me how about in distributed system, many machine,
many log files, how to find the userid, who has max clicks in one section
2 variables in table1 showing all records of transactions each customer
makes. one customer (usedid 001) may purchase same products (productid Q909)
several times.
table1:
userid productid
001 Q909
001 Q909
001 Q908
002 Q101
Question: use SQL to calculate how many customers purchase only 1 type of
product, 2 types of products, 3...and so on.
for example: if there were 694 unique userid who purchased only 1 unique
productid, 900 unique userid purchased only 2 unique productid, we wan... 阅读全帖
If the system doesn't support distinct count, do the following subquery
Select userid, count(productid) as ProdCount from
(select distinct userid, productid from Table)
group by userid
select NoOfType as number_of_type_product_purchased, count(user) as number_
of_customer
from (
select userid, count(productid) as NoOfType
from (select distinct userid productid
from table1) a
group by userid) a1
group by NoOfType
order by number_of_type_product_purchased
SELECT `cnt` AS number_of_type_product_purchased, COUNT(`userid`) AS number_
of_customer FROM
(
SELECT `userid`,COUNT(`productid`) AS cnt FROM `table1`
GROUP BY `userid`
) TMP
GROUP BY `cnt`
就是前几天有人贴的这个题吧：
Userid PageID
A 1
A 2
A 3
B 2
B 3
C 1
B 4
A 4
找出最常用的length-3访问序列：对于用户A：1-2-3， 2-3-4 用户B：2-3-4
2-3-4 是最常见的
两个hash，一个user hash，每一项存userid和对应的三连击中的前两个值；一个三连
击hash，存三连击string和count。
对于logfile中的每一行，在这两个hash中查找并更新。如果认为每次hash的复杂度为O
(1),则总的时间复杂度为O(n)。空间复杂度为O(m+k)，m为userid的个数，k为不同的三
连击的个数。
